package com.iris.live.services.services.impl;

import com.google.common.base.Strings;
import com.iris.live.services.common.GetConfigureInfoUtils;
import com.iris.live.services.common.JsonObjectUtils;
import com.iris.live.services.common.StringUtils;
import com.iris.live.services.common.reconsitution.BeanUtils;
import com.iris.live.services.common.reconsitution.ExcelAnalysisUtil;
import com.iris.live.services.data.models.*;
import com.iris.live.services.data.repositories.*;
import com.iris.live.services.data.repositories.impl.BaseRepository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.models.RequestBase;
import com.iris.live.services.models.ResultData;
import com.iris.live.services.report.ExcelReportContext;
import com.iris.live.services.report.IExcelReportService;
import com.iris.live.services.report.IExcelReportSource;
import com.iris.live.services.services.DataCheckService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 数据核检
 * @author wangzhenyan
 * 2017/5/19 15:02
 */
@Service
public class DataCheckServiceImpl extends BaseRepository implements DataCheckService {

    // 金融利率专用误差
    private static final double DEFAULT_Difference_Yearly_Rate = 0.01;
    //金额默认误差
    private static final double DEFAULT_Difference_Value = 0.5;
    //金融扣分基数
    private static final int DEFAULT_Deduction_Finance = -7;
    //金融底分
    private static final int DEFAULT_Basic_Finance = 9;
    //保险扣分基数
    private static final int DEFAULT_Deduction_Insurance = -6;
    //保险底分
    private static final int DEFAULT_Basic_Insurance = 10;
    private static Logger logger = LoggerFactory.getLogger(DataCheckServiceImpl.class);
    @Resource
    private FinanceCheckRepository financeCheckRepository;
    @Resource
    private InsuranceCheckRepository insuranceCheckRepository;
    @Resource
    private SalesAdminCheckRepository saAdChRep;
    @Resource
    private SalesConsultantCheckRepository scChRep;
    @Resource
    private SalesAdminCheckRepositorySql saAdChRepSq;
    @Resource
    private CheckFinanceRepositorySql checkFinanceSql;
    @Resource
    private CheckInsuranceRepositorySql checkInsuranceSql;
    @Resource
    private 用户Repository userRep;
    @Resource
    private FinanceCheckRepositorySql financeCheckRepositorySql;
    @Resource
    private InsuranceCheckRepositorySql insuranceCheckRepositorySql;
    @Resource
    private SalesConsultantCheckRepositorySql salesConsultantCheckRepositorySql;
    @Resource(name="exportFinanceRankingSource")
    private IExcelReportSource exportFinanceRankingSource;
    @Resource(name = "exportInsuranceRankingSource")
    private IExcelReportSource exportInsuranceRankingSource;
    @Resource(name = "exportSalesAdminRankingSource")
    private IExcelReportSource exportSalesAdminRankingSource;
    @Resource(name = "exportSalesConsultantRankingSource")
    private IExcelReportSource exportSalesConsultantRankingSource;

    @Resource
    private IExcelReportService excelReportService;

    @Resource(name = "ExportDataCheckScFalseRateReportSource")
    private IExcelReportSource ExportDataCheckScFalseRateReportSource;

    @Resource(name = "ExportDataCheckFinanceFalseRateReportSource")
    private IExcelReportSource ExportDataCheckFinanceFalseRateReportSource;

    @Resource(name = "ExportDataCheckInsuranceFalseRateReportSource")
    private IExcelReportSource ExportDataCheckInsuranceFalseRateReportSource;

    @Resource(name = "ExportDataCheckScAdminFalseRateReportSource")
    private IExcelReportSource ExportDataCheckScAdminFalseRateReportSource;

    @Resource(name = "ExportDataCheckScContrastReportSource")
    private IExcelReportSource ExportDataCheckScContrastReportSource;

    @Resource(name = "ExportDataCheckFinanceContrastReportSource")
    private IExcelReportSource ExportDataCheckFinanceContrastReportSource;

    @Resource(name = "ExportDataCheckInsuranceContrastReportSource")
    private IExcelReportSource ExportDataCheckInsuranceContrastReportSource;

    @Resource(name = "ExportDataCheckScAdminContrastReportSource")
    private IExcelReportSource ExportDataCheckScAdminContrastReportSource;

    @Resource(name = "ExportDataCheckScRankingListReportSource")
    private IExcelReportSource ExportDataCheckScRankingListReportSource;

    @Resource(name = "ExportDataCheckFinanceRankingListReportSource")
    private IExcelReportSource ExportDataCheckFinanceRankingListReportSource;

    @Resource(name = "ExportDataCheckInsuranceRankingListReportSource")
    private IExcelReportSource ExportDataCheckInsuranceRankingListReportSource;

    @Resource(name = "ExportDataCheckScAdminRankingListReportSource")
    private IExcelReportSource ExportDataCheckScAdminRankingListReportSource;

    @Autowired
    private DataCheckRepository dataCheckRepository;

    @Autowired
    private Lsh经销商表Repository lsh经销商表Repository;

    @Override
    @Transactional(rollbackFor=Exception.class)
    public ResultData importFinanceData(MultipartFile file, String userName, String permit, String nodeName) throws Exception {
        ResultData result = new ResultData("true","导入成功",null);
        //从excle获取数据
        Map excelMap = ExcelAnalysisUtil.validExcel(file, nodeName);
        if (excelMap != null && excelMap.get("isSuccess") != null && "false".equals(excelMap.get("isSuccess"))) {
            result.setResult("false");
            result.setMessage("导入失败。" + excelMap.get("errorMessage").toString());
            logger.error(String.format(excelMap.get("errorMessage").toString() + "经销商许可为:%s",permit));
            return result;
        }
        // excel数据集合
        List<Map<String,Object>> mapList =  (List<Map<String, Object>>) excelMap.get("list");

        // 验证Excel中是否有重复数据
        if (validRepeatedData(result, mapList)) return result;

        //导入批次
        String importBatch = importBatchSetting();

        用户Model user = userRep.findBy许可And用户名(permit, userName);
        if (validUser(permit, result, user)) return result;
        List<TFinanceCheckModel> financeList = financeCheckRepositorySql.findFinanceCheckByImportBatchAndUser(importBatch,user);
        //判断是否为当天第一次导入
        if (null != financeList && !(financeList.isEmpty())) {
            result.setResult("false");
            result.setMessage("导入失败。当天只能导入一次!");
            return result;
        }

        //Excel不为空
        if (null != mapList && !mapList.isEmpty()) {
            //excel数据转为对象
            List<Object> objectList = BeanUtils.mapToWrapperObjectList(mapList,TFinanceCheckModel.class);
            for (Object model : objectList) {
                TFinanceCheckModel financeCheckModel = (TFinanceCheckModel) model;
                //插入记录
                financeCheckModel.setImportBatch(importBatch);   //导入批次
                financeCheckModel.setPermit(permit);  //许可
                financeCheckModel.setEvaluationUser(userName);  //考评用户
                financeCheckModel.setImportTime(new Timestamp(new Date().getTime()));  //导入时间
                financeCheckModel.setImportUser(userName);  //导入用户
                financeCheckRepository.save(financeCheckModel);
            }
        }

        // 调用评分方法
        ResultData rank = rankFinanceData(permit,userName);
        if (rank != null && "true".equals(rank.getResult())) {
            return result;
        } else {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();  // 回滚事务
            result.setResult("false");
            result.setMessage("导入失败。评分出现异常!");
            logger.error(String.format("导入失败。评分出现异常!经销商许可为:%s",permit));
            return result;
        }
    }

    @Override
    @Transactional(rollbackFor=Exception.class)
    public ResultData importInsuranceData(MultipartFile file, String userName, String permit, String nodeName) throws Exception {
        ResultData result = new ResultData("true","导入成功",null);
        //从excle获取数据
        Map excelMap = ExcelAnalysisUtil.validExcel(file, nodeName);
        if (excelMap != null && excelMap.get("isSuccess") != null && "false".equals(excelMap.get("isSuccess"))) {
            result.setResult("false");
            result.setMessage("导入失败。" + excelMap.get("errorMessage").toString());
            logger.error(String.format(excelMap.get("errorMessage").toString() + "经销商许可为:%s",permit));
            return result;
        }
        // excel数据集合
        List<Map<String,Object>> mapList =  (List<Map<String, Object>>) excelMap.get("list");

        // 验证Excel中是否有重复数据
        if (validRepeatedData(result, mapList)) return result;

        //导入批次
        String importBatch = importBatchSetting();

        用户Model user = userRep.findBy许可And用户名(permit, userName);
        if (validUser(permit, result, user)) return result;
        List<TInsuranceCheckModel> insuranceList = insuranceCheckRepositorySql.findInsuranceCheckByImportBatchAndUser(importBatch,user);
        //判断是否为当天第一次导入
        if (null != insuranceList && !(insuranceList.isEmpty())) {
            result.setResult("false");
            result.setMessage("当天只能导入一次!");
            return result;
        }

        //excel不为空
        if (null != mapList && !mapList.isEmpty()) {
            //excel数据转为对象
            List<Object> objectList = BeanUtils.mapToWrapperObjectList(mapList,TInsuranceCheckModel.class);
            for (Object model : objectList) {
                TInsuranceCheckModel insuranceCheckModel = (TInsuranceCheckModel) model;
                //插入记录
                insuranceCheckModel.setImportBatch(importBatch);   //导入批次
                insuranceCheckModel.setPermit(permit);  //许可
                insuranceCheckModel.setEvaluationUser(userName);  //考评用户
                insuranceCheckModel.setImportTime(new Timestamp(new Date().getTime()));  //导入时间
                insuranceCheckModel.setImportUser(userName);  //导入用户
                insuranceCheckRepository.save(insuranceCheckModel);
            }
        }

        // 调用评分方法
        ResultData rank = rankInsuranceData(permit,userName);
        if (rank != null && "true".equals(rank.getResult())) {
            return result;
        } else {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();  // 回滚事务
            result.setResult("false");
            result.setMessage("导入失败。评分出现异常!");
            logger.error(String.format("导入失败。评分出现异常!经销商许可为:%s",permit));
            return result;
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResultData importSalesAdminData(MultipartFile file, String userName, String permit, String nodeName) throws Exception {
        ResultData result = new ResultData("true","导入成功",null);
        //从excle获取数据
        Map excelMap = ExcelAnalysisUtil.validExcel(file, nodeName);
        if (excelMap != null && excelMap.get("isSuccess") != null && "false".equals(excelMap.get("isSuccess"))) {
            result.setResult("false");
            result.setMessage("导入失败。" + excelMap.get("errorMessage").toString());
            logger.error(String.format(excelMap.get("errorMessage").toString() + "经销商许可为:%s",permit));
            return result;
        }
        // excel数据集合
        List<Map<String,Object>> mapList =  (List<Map<String, Object>>) excelMap.get("list");

        // 验证Excel中是否有重复数据
        if (validRepeatedData(result, mapList)) return result;

        //导入批次
        String importBatch = importBatchSetting();

        用户Model user = userRep.findBy许可And用户名(permit, userName);
        if (validUser(permit, result, user)) return result;
        List<TSalesAdminCheckModel> salesAdminList = saAdChRepSq.getScSysDataByImportBatchAndUser(importBatch,user);
//        判断是否为当天第一次导入
        if (null != salesAdminList && !(salesAdminList.isEmpty())) {
            result.setResult("false");
            result.setMessage("导入失败。当天只能导入一次!");
            return result;
        }

        //excel不为空
        if (null != mapList && !mapList.isEmpty()) {
            //excel数据转为对象
            List<Object> objectList = BeanUtils.mapToWrapperObjectList(mapList,TSalesAdminCheckModel.class);
            for (Object model : objectList) {
                TSalesAdminCheckModel salesAdminCheckModel = (TSalesAdminCheckModel) model;
                //插入记录
                salesAdminCheckModel.setImportBatch(importBatch);   //导入批次
                salesAdminCheckModel.setPermit(permit);  //许可
                salesAdminCheckModel.setEvaluationUser(userName);  //考评用户
                salesAdminCheckModel.setImportTime(new Timestamp(new Date().getTime()));  //导入时间
                salesAdminCheckModel.setImportUser(userName);  //导入用户
                saAdChRep.save(salesAdminCheckModel);
            }
        }

        // 调用评分方法
        ResultData rank = salesAdminInputDataCheck(permit,importBatch,userName);
        if (rank != null && "true".equals(rank.getResult())) {
            return result;
        } else {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();  // 回滚事务
            result.setResult("false");
            result.setMessage("导入失败。评分出现异常!");
            logger.error(String.format("导入失败。评分出现异常!经销商许可为:%s",permit));
            return result;
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public ResultData importSalesConsultantData(MultipartFile file, String userName, String permit, String nodeName) throws Exception {
        ResultData result = new ResultData("true","导入成功",null);
        //从excle获取数据
        Map excelMap = ExcelAnalysisUtil.validExcel(file, nodeName);
        if (excelMap != null && excelMap.get("isSuccess") != null && "false".equals(excelMap.get("isSuccess"))) {
            result.setResult("false");
            result.setMessage("导入失败。" + excelMap.get("errorMessage").toString());
            logger.error(String.format(excelMap.get("errorMessage").toString() + "经销商许可为:%s",permit));
            return result;
        }
            // excel数据集合
        List<Map<String,Object>> mapList =  (List<Map<String, Object>>) excelMap.get("list");

        // 验证Excel中是否有重复数据
        if (validSCRepeatedData(result, mapList)) return result;

        //导入批次
        String importBatch = importBatchSetting();

        用户Model user = userRep.findBy许可And用户名(permit, userName);
        if (validUser(permit, result, user)) return result;
        List<TSalesConsultantCheckModel> salesConsultantList = salesConsultantCheckRepositorySql.findSalesConsultantCheckByImportBatchAndUser(importBatch,user);
        //判断是否为当天第一次导入
        if (null != salesConsultantList && !(salesConsultantList.isEmpty())) {
            result.setResult("false");
            result.setMessage("导入失败。当天只能导入一次!");
            return result;
        }

        //excel不为空
        if (null != mapList && !mapList.isEmpty()) {
            //excel数据转为对象
            List<Object> objectList = BeanUtils.mapToWrapperObjectList(mapList,TSalesConsultantCheckModel.class);
            for (Object model : objectList) {
                TSalesConsultantCheckModel salesConsultantCheckModel = (TSalesConsultantCheckModel) model;

                String vin = salesConsultantCheckModel.getVin(); // 车架号
                //订单 (车架号不为空，则为订单)
                if (!StringUtils.isNullOrBlank(vin)) {
                    if (vin.startsWith("#")) {
                        salesConsultantCheckModel.setVin(vin.replaceFirst("#",""));   // 去掉开头的"#",则剩下的为生产号
                    }
                }

                //插入记录
                salesConsultantCheckModel.setImportBatch(importBatch);   //导入批次
                salesConsultantCheckModel.setPermit(permit);  //许可
//            salesConsultantCheckModel.setEvaluationUser(userName);  //考评用户
                salesConsultantCheckModel.setImportTime(new Timestamp(new Date().getTime()));  //导入时间
                salesConsultantCheckModel.setImportUser(userName);  //导入用户
                scChRep.save(salesConsultantCheckModel);
            }
        }

        // 调用评分方法
        ResultData rank = scInputDataCheck(permit,importBatch,userName);
        if (rank != null && "true".equals(rank.getResult())) {
            return result;
        } else {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();  // 回滚事务
            result.setResult("false");
            result.setMessage("导入失败。评分出现异常!");
            logger.error(String.format("导入失败。评分出现异常!经销商许可为:%s",permit));
            return result;
        }
    }

    /**
     * 销售行政数据检核，用户导入EXCEL，数据成功入库后，即调用本方法计算当前批次的数据与库里数据的比较结果
     *
     * @param permit 待处理的许可
     * @param batch  待处理的批次
     * @param user   待考评用户
     * @return 标准返回结果
     */
    private ResultData salesAdminInputDataCheck(String permit, String batch, String user) throws Exception {
        ResultData result = new ResultData();
        logger.info("salesAdminInputDataCheck方法传入参数permit=" + permit + ",batch=" + batch);
        try {
            // 1.数据准备
            // 取出数据库中，permit下，已处于零售状态的车架号
            List sysList = saAdChRepSq.getSaAdSysDataByPermitAndDate(permit);
            // 取出数据库中，数据检核表中当天导入的数据
            List<TSalesAdminCheckModel> imList = saAdChRep.findImportedSa(permit, batch);
            // 临时用来存放sysList中存在，imList中不存在的VIN
            List<TSalesAdminCheckModel> tmpList = new ArrayList<>();
            for (Object o : sysList) {
                Map m = (Map) o;
                int i = 0;
                for (; i < imList.size(); i++) {
                    TSalesAdminCheckModel saAdChMo = imList.get(i);
                    if (m.get("vin").equals(saAdChMo.getVin())) { // VIN匹配，将sysList model里的数据填充到imList对应的model中
                        saAdChMo.setcInStockDt((java.sql.Date) m.get("in_stock_dt"));
                        saAdChMo.setcOutStockDt((java.sql.Date) m.get("out_stock_dt"));
                        saAdChMo.setcReDt((java.sql.Date) m.get("re_dt"));
                        saAdChMo.setcOrderCusName((String) m.get("order_cus_name"));
                        saAdChMo.setcInvoiceUname((String) m.get("invoice_uname"));
                        saAdChMo.setcPriceTi((BigDecimal) m.get("price_ti"));
                        saAdChMo.setcFreebyIn((BigDecimal) m.get("freeby_in"));
                        saAdChMo.setcFreebySubInt((BigDecimal) m.get("freeby_sub_int"));
                        saAdChMo.setcFreebyA((BigDecimal) m.get("freeby_a"));
                        saAdChMo.setcFreebyB((BigDecimal) m.get("freeby_b"));
                        saAdChMo.setcFreebySi((BigDecimal) m.get("freeby_si"));
                        saAdChMo.setcFreebyBl((BigDecimal) m.get("freeby_bl"));
                        saAdChMo.setcFreebySh((BigDecimal) m.get("freeby_sh"));
                        saAdChMo.setcFreebyOi((BigDecimal) m.get("freeby_oi"));
                        saAdChMo.setcFreebyIsIn((String) m.get("freeby_is_in"));
                        saAdChMo.setcFreebyIsSssc((String) m.get("freeby_is_sssc"));
                        saAdChMo.setcFreebyIsSd((String) m.get("freeby_is_sd"));
                        saAdChMo.setcFreebyIsOil((String) m.get("freeby_is_oil"));
                        saAdChMo.setcFreebyIsPre((String) m.get("freeby_is_pre"));
                        saAdChMo.setcFreebyIsTi((String) m.get("freeby_is_ti"));
                        saAdChMo.setcPurTaxSi((String) m.get("pur_tax_si"));
                        saAdChMo.setcReSi((BigDecimal) m.get("re_si"));
                        saAdChMo.setcWhSi((BigDecimal) m.get("wh_si"));
                        saAdChMo.setCompareResult("02");
                        break;
                    } // VIN不匹配
                }
                if (i == imList.size()) { // 如果在imList中找不到匹配的VIN，说明此车没有包含在导入的EXCEL中，作为新记录入库
                    TSalesAdminCheckModel mo = new TSalesAdminCheckModel();
                    mo.setImportBatch(batch);
                    mo.setPermit(permit);
                    mo.setVin((String) m.get("vin"));
                    mo.setcInStockDt((java.sql.Date) m.get("in_stock_dt"));
                    mo.setcOutStockDt((java.sql.Date) m.get("out_stock_dt"));
                    mo.setcReDt((java.sql.Date) m.get("re_dt"));
                    mo.setcOrderCusName((String) m.get("order_cus_name"));
                    mo.setcInvoiceUname((String) m.get("invoice_uname"));
                    mo.setcPriceTi((BigDecimal) m.get("price_ti"));
                    mo.setcFreebyIn((BigDecimal) m.get("freeby_in"));
                    mo.setcFreebySubInt((BigDecimal) m.get("freeby_sub_int"));
                    mo.setcFreebyA((BigDecimal) m.get("freeby_a"));
                    mo.setcFreebyB((BigDecimal) m.get("freeby_b"));
                    mo.setcFreebySi((BigDecimal) m.get("freeby_si"));
                    mo.setcFreebyBl((BigDecimal) m.get("freeby_bl"));
                    mo.setcFreebySh((BigDecimal) m.get("freeby_sh"));
                    mo.setcFreebyOi((BigDecimal) m.get("freeby_oi"));
                    mo.setcFreebyIsIn((String) m.get("freeby_is_in"));
                    mo.setcFreebyIsSssc((String) m.get("freeby_is_sssc"));
                    mo.setcFreebyIsSd((String) m.get("freeby_is_sd"));
                    mo.setcFreebyIsOil((String) m.get("freeby_is_oil"));
                    mo.setcFreebyIsPre((String) m.get("freeby_is_pre"));
                    mo.setcFreebyIsTi((String) m.get("freeby_is_ti"));
                    mo.setcPurTaxSi((String) m.get("pur_tax_si"));
                    mo.setcReSi((BigDecimal) m.get("re_si"));
                    mo.setcWhSi((BigDecimal) m.get("wh_si"));
                    mo.setCompareResult("03");
                    tmpList.add(mo);
                }
                // imList中存在，sysList中不存在的数据，已经在库中了，并且相应的c字段（比较字段）都为null，不需要额外处理就可以进入下阶段
            }
            // 将tmpList中的数据添加到imList中
            imList.addAll(tmpList);
            logger.info("salesAdminInputDataCheck中tmpList为：" + JsonObjectUtils.writeAsJson(tmpList));

            // 2.处理imList中的分数计算
            if (!imList.isEmpty()) { // 非空时正常计算分数
                for (TSalesAdminCheckModel mo : imList) {
                    BigDecimal zero = new BigDecimal("0.0"); // 销售行政字段相同时扣0分
                    BigDecimal de = new BigDecimal("-4.0"); // 字段不同扣3分
                    BigDecimal tolerance = new BigDecimal("0.5"); // 数值类型比较时，差值0.5内可以认为相同
                    DecimalFormat format = new DecimalFormat("#");
                    // EXCEL中未填入的数据被解析成NULL存入数据库，如果两个值同时为NULL，不扣分，两值相等也不扣分
                    if ("02".equals(mo.getCompareResult())) {
                        if ((mo.getInStockDt() == null && mo.getcInStockDt() == null) || (mo.getInStockDt() != null && mo.getcInStockDt() != null && mo.getInStockDt().equals(mo.getcInStockDt()))) {
                            mo.setdInStockDt(zero);
                        } else {
                            mo.setdInStockDt(de);
                        }
                        if ((mo.getOutStockDt() == null && mo.getcOutStockDt() == null) || (mo.getOutStockDt() != null && mo.getcOutStockDt() != null && mo.getOutStockDt().equals(mo.getcOutStockDt()))) {
                            mo.setdOutStockDt(zero);
                        } else {
                            mo.setdOutStockDt(de);
                        }
                        if ((mo.getReDt() == null && mo.getcReDt() == null) || (mo.getReDt() != null && mo.getcReDt() != null && mo.getReDt().equals(mo.getcReDt()))) {
                            mo.setdReDt(zero);
                        } else {
                            mo.setdReDt(de);
                        }
                        if ((mo.getOrderCusName() == null && mo.getcOrderCusName() == null) || (mo.getOrderCusName() != null && mo.getcOrderCusName() != null && mo.getOrderCusName().equals(mo.getcOrderCusName()))) {
                            mo.setdOrderCusName(zero);
                        } else {
                            mo.setdOrderCusName(de);
                        }
                        if ((mo.getInvoiceUname() == null && mo.getcInvoiceUname() == null) || (mo.getInvoiceUname() != null && mo.getcInvoiceUname() != null && mo.getInvoiceUname().equals(mo.getcInvoiceUname()))) {
                            mo.setdInvoiceUname(zero);
                        } else {
                            mo.setdInvoiceUname(de);
                        }
                        if ((mo.getPriceTi() == null && mo.getcPriceTi() == null) || (mo.getPriceTi() != null && mo.getcPriceTi() != null && mo.getPriceTi().subtract(mo.getcPriceTi()).abs().compareTo(tolerance) <= 0)
                                || (mo.getPriceTi() == null && mo.getcPriceTi() != null && "0".equals(format.format(mo.getcPriceTi())))
                                || (mo.getcPriceTi() == null && mo.getPriceTi() != null && "0".equals(format.format(mo.getPriceTi()))) ) {
                            mo.setdPriceTi(zero);
                        } else {
                            mo.setdPriceTi(de);
                        }
                        if ((mo.getFreebyIn() == null && mo.getcFreebyIn() == null) || (mo.getFreebyIn() != null && mo.getcFreebyIn() != null && mo.getFreebyIn().subtract(mo.getcFreebyIn()).abs().compareTo(tolerance) <= 0)
                                || (mo.getFreebyIn() == null && mo.getcFreebyIn() != null && "0".equals(format.format(mo.getcFreebyIn())))
                                || (mo.getcFreebyIn() == null && mo.getFreebyIn() != null && "0".equals(format.format(mo.getFreebyIn()))) ) {
                            mo.setdFreebyIn(zero);
                        } else {
                            mo.setdFreebyIn(de);
                        }
                        if ((mo.getFreebySubInt() == null && mo.getcFreebySubInt() == null) || (mo.getFreebySubInt() != null && mo.getcFreebySubInt() != null && mo.getFreebySubInt().subtract(mo.getcFreebySubInt()).abs().compareTo(tolerance) <= 0)
                                || (mo.getFreebySubInt() == null && mo.getcFreebySubInt() != null && "0".equals(format.format(mo.getcFreebySubInt())))
                                || (mo.getcFreebySubInt() == null && mo.getFreebySubInt() != null && "0".equals(format.format(mo.getFreebySubInt()))) ) {
                            mo.setdFreebySubInt(zero);
                        } else {
                            mo.setdFreebySubInt(de);
                        }
                        if ((mo.getFreebyA() == null && mo.getcFreebyA() == null) || (mo.getFreebyA() != null && mo.getcFreebyA() != null && mo.getFreebyA().subtract(mo.getcFreebyA()).abs().compareTo(tolerance) <= 0)
                                || (mo.getFreebyA() == null && mo.getcFreebyA() != null && "0".equals(format.format(mo.getcFreebyA())))
                                || (mo.getcFreebyA() == null && mo.getFreebyA() != null && "0".equals(format.format(mo.getFreebyA()))) ) {
                            mo.setdFreebyA(zero);
                        } else {
                            mo.setdFreebyA(de);
                        }
                        if ((mo.getFreebyB() == null && mo.getcFreebyB() == null) || (mo.getFreebyB() != null && mo.getcFreebyB() != null && mo.getFreebyB().subtract(mo.getcFreebyB()).abs().compareTo(tolerance) <= 0)
                                || (mo.getFreebyB() == null && mo.getcFreebyB() != null && "0".equals(format.format(mo.getcFreebyB())))
                                || (mo.getcFreebyB() == null && mo.getFreebyB() != null && "0".equals(format.format(mo.getFreebyB()))) ) {
                            mo.setdFreebyB(zero);
                        } else {
                            mo.setdFreebyB(de);
                        }
                        if ((mo.getFreebySi() == null && mo.getcFreebySi() == null) || (mo.getFreebySi() != null && mo.getcFreebySi() != null && mo.getFreebySi().subtract(mo.getcFreebySi()).abs().compareTo(tolerance) <= 0)
                                || (mo.getFreebySi() == null && mo.getcFreebySi() != null && "0".equals(format.format(mo.getcFreebySi())))
                                || (mo.getcFreebySi() == null && mo.getFreebySi() != null && "0".equals(format.format(mo.getFreebySi()))) ) {
                            mo.setdFreebySi(zero);
                        } else {
                            mo.setdFreebySi(de);
                        }
                        if ((mo.getFreebyBl() == null && mo.getcFreebyBl() == null) || (mo.getFreebyBl() != null && mo.getcFreebyBl() != null && mo.getFreebyBl().subtract(mo.getcFreebyBl()).abs().compareTo(tolerance) <= 0)
                                || (mo.getFreebyBl() == null && mo.getcFreebyBl() != null && "0".equals(format.format(mo.getcFreebyBl())))
                                || (mo.getcFreebyBl() == null && mo.getFreebyBl() != null && "0".equals(format.format(mo.getFreebyBl()))) ) {
                            mo.setdFreebyBl(zero);
                        } else {
                            mo.setdFreebyBl(de);
                        }
                        if ((mo.getFreebySh() == null && mo.getcFreebySh() == null) || (mo.getFreebySh() != null && mo.getcFreebySh() != null && mo.getFreebySh().subtract(mo.getcFreebySh()).abs().compareTo(tolerance) <= 0)
                                || (mo.getFreebySh() == null && mo.getcFreebySh() != null && "0".equals(format.format(mo.getcFreebySh())))
                                || (mo.getcFreebySh() == null && mo.getFreebySh() != null && "0".equals(format.format(mo.getFreebySh()))) ) {
                            mo.setdFreebySh(zero);
                        } else {
                            mo.setdFreebySh(de);
                        }
                        if ((mo.getFreebyOi() == null && mo.getcFreebyOi() == null) || (mo.getFreebyOi() != null && mo.getcFreebyOi() != null && mo.getFreebyOi().subtract(mo.getcFreebyOi()).abs().compareTo(tolerance) <= 0)
                                || (mo.getFreebyOi() == null && mo.getcFreebyOi() != null && "0".equals(format.format(mo.getcFreebyOi())))
                                || (mo.getcFreebyOi() == null && mo.getFreebyOi() != null && "0".equals(format.format(mo.getFreebyOi()))) ) {
                            mo.setdFreebyOi(zero);
                        } else {
                            mo.setdFreebyOi(de);
                        }
                        if (mo.getFreebyIsIn().equals(mo.getcFreebyIsIn())) {
                            mo.setdFreebyIsIn(zero);
                        } else {
                            mo.setdFreebyIsIn(de);
                        }
                        if (mo.getFreebyIsSssc().equals(mo.getcFreebyIsSssc())) {
                            mo.setdFreebyIsSssc(zero);
                        } else {
                            mo.setdFreebyIsSssc(de);
                        }
                        if (mo.getFreebyIsSd().equals(mo.getcFreebyIsSd())) {
                            mo.setdFreebyIsSd(zero);
                        } else {
                            mo.setdFreebyIsSd(de);
                        }
                        if (mo.getFreebyIsOil().equals(mo.getcFreebyIsOil())) {
                            mo.setdFreebyIsOil(zero);
                        } else {
                            mo.setdFreebyIsOil(de);
                        }
                        if (mo.getFreebyIsPre().equals(mo.getcFreebyIsPre())) {
                            mo.setdFreebyIsPre(zero);
                        } else {
                            mo.setdFreebyIsPre(de);
                        }
                        if (mo.getFreebyIsTi().equals(mo.getcFreebyIsTi())) {
                            mo.setdFreebyIsTi(zero);
                        } else {
                            mo.setdFreebyIsTi(de);
                        }
                        if (mo.getPurTaxSi().equals(mo.getcPurTaxSi())) {
                            mo.setdPurTaxSi(zero);
                        } else {
                            mo.setdPurTaxSi(de);
                        }
                        if ((mo.getReSi() == null && mo.getcReSi() == null) || (mo.getReSi() != null && mo.getcReSi() != null && mo.getReSi().subtract(mo.getcReSi()).abs().compareTo(tolerance) <= 0)
                                || (mo.getReSi() == null && mo.getcReSi() != null && "0".equals(format.format(mo.getcReSi())))
                                || (mo.getcReSi() == null && mo.getReSi() != null && "0".equals(format.format(mo.getReSi()))) ) {
                            mo.setdReSi(zero);
                        } else {
                            mo.setdReSi(de);
                        }
                        if ((mo.getWhSi() == null && mo.getcWhSi() == null) || (mo.getWhSi() != null && mo.getcWhSi() != null && mo.getWhSi().subtract(mo.getcWhSi()).abs().compareTo(tolerance) <= 0)
                                || (mo.getWhSi() == null && mo.getcWhSi() != null && "0".equals(format.format(mo.getcWhSi())))
                                || (mo.getcWhSi() == null && mo.getWhSi() != null && "0".equals(format.format(mo.getWhSi())))  ) {
                            mo.setdWhSi(zero);
                        } else {
                            mo.setdWhSi(de);
                        }
                    }
                    if ("00".equals(mo.getCompareResult())) { // 此处还没有更新比较结果的数据，就是导入有，实际无的数据
                        mo.setCompareResult("01");
                    }
                }
            } else { // 为空时属于空表导入，生成虚拟数据记满分
                TSalesAdminCheckModel mo = new TSalesAdminCheckModel();
                mo.setImportBatch(batch);
                mo.setPermit(permit);
                mo.setVin("");
                mo.setTotalScore(new BigDecimal("100"));
                mo.setCompareResult("04");
                mo.setImportUser(user);  // 插入用户
                mo.setImportTime(new Timestamp(new Date().getTime())); // 插入时间
                saAdChRep.save(mo);
                logger.warn("单日批次销售行政数据为空, Dealer={}, 批次={}", permit, batch);
            }
            // 3.计算总分
            for (TSalesAdminCheckModel mo : imList) {
                BigDecimal total = new BigDecimal("100"); // 百分制评分
                if ("02".equals(mo.getCompareResult())) { // 匹配
                    total = total.add(mo.getdInStockDt());
                    total = total.add(mo.getdOutStockDt());
                    total = total.add(mo.getdReDt());
                    total = total.add(mo.getdOrderCusName());
                    total = total.add(mo.getdInvoiceUname());
                    total = total.add(mo.getdPriceTi());
                    total = total.add(mo.getdFreebyIn());
                    total = total.add(mo.getdFreebySubInt());
                    total = total.add(mo.getdFreebyA());
                    total = total.add(mo.getdFreebyB());
                    total = total.add(mo.getdFreebySi());
                    total = total.add(mo.getdFreebyBl());
                    total = total.add(mo.getdFreebySh());
                    total = total.add(mo.getdFreebyOi());
                    total = total.add(mo.getdFreebyIsIn());
                    total = total.add(mo.getdFreebyIsSssc());
                    total = total.add(mo.getdFreebyIsSd());
                    total = total.add(mo.getdFreebyIsOil());
                    total = total.add(mo.getdFreebyIsPre());
                    total = total.add(mo.getdFreebyIsTi());
                    total = total.add(mo.getdPurTaxSi());
                    total = total.add(mo.getdReSi());
                    total = total.add(mo.getdWhSi());
                } else { // 不匹配，给0分
                    total = new BigDecimal("0");
                }
                // 写入导入用户
                mo.setImportUser(user);
                mo.setImportTime(new Timestamp(new Date().getTime()));  // 插入时间
                mo.setTotalScore(total);
            }
            // 4.保存impList
            saAdChRep.save(imList);
        } catch (Exception e) {
            result.setResult("false");
            result.setMessage("评分出现异常");
            logger.error("rankSalesAdminData出错,Dealer={}",permit,e);
        }
        return result;
    }

    // 对销售顾问评分
    private ResultData scInputDataCheck(String permit, String batch, String user) throws Exception {
        ResultData result = new ResultData();
        logger.info("scInputDataCheck传入参数permit=" + permit + ",batch=" + batch);
        try {
            // 1.数据准备
            // 取出数据库中，permit下，从提交意向订单开始，订单，合同，开票，交车等环节的数据
            List sysList = saAdChRepSq.getScSysDataByPermitAndDate(permit);
            // 取出数据库中，数据检核表中当天导入的数据
            List<TSalesConsultantCheckModel> imList = saAdChRep.findImportedSc(permit, batch);
            // 临时用来存放sysList中存在，imList中不存在的VIN
            List<TSalesConsultantCheckModel> tmpList = new ArrayList<>();
            for (Object o : sysList) {
                Map m = (Map) o;
                int i = 0;
                for (; i < imList.size(); i++) {
                    TSalesConsultantCheckModel scMo = imList.get(i);
                    if (m.get("custom_tel").equals(scMo.getCustomTel())
                            && (
                            ((m.get("prod_num") != null && m.get("prod_num").equals(scMo.getVin())) // 生产号匹配
                                    || (m.get("vin") != null && m.get("vin").equals(scMo.getVin()))) // 车架号匹配
                                    ||
                                    ((m.get("vin") == null && m.get("prod_num") == null) // 意向订单以电话+意向车型+意向金作为key
                                            && m.get("intent_car").equals(scMo.getIntentCar())
                                            && scMo.getIoAmount().compareTo((BigDecimal) m.get("io_amount")) == 0)
                    )
                            ) {
                        // 如果匹配，将系统中的销售状态，写到表中，EXCEL中的销售状态弃用
                        scMo.setSalesStatus((String) m.get("sales_status"));
                        scMo.setcIoDt((java.sql.Date) m.get("io_dt"));
                        scMo.setcOrderCusName((String) m.get("order_cus_name"));
                        scMo.setcIoAmount((BigDecimal) m.get("io_amount"));
                        scMo.setcContractSignedDt((java.sql.Date) m.get("contract_signed_dt"));
                        scMo.setcInvoiceDt((java.sql.Date) m.get("invoice_dt"));
                        scMo.setcInvoiceUname((String) m.get("invoice_uname"));
                        scMo.setcDeliverDt((java.sql.Date) m.get("deliver_dt"));
                        scMo.setcPriceTi((BigDecimal) m.get("price_ti"));
                        scMo.setcFreebyIn((BigDecimal) m.get("freeby_in"));
                        scMo.setcFreebySubInt((BigDecimal) m.get("freeby_sub_int"));
                        scMo.setcFreebyA(m.get("freeby_a") == null ? null : ((BigInteger) m.get("freeby_a")).intValue());
                        scMo.setcFreebyB(m.get("freeby_b") == null ? null : ((BigInteger) m.get("freeby_b")).intValue());
                        scMo.setcFreebySi((String) m.get("freeby_si"));
                        scMo.setcFreebyBl((String) m.get("freeby_bl"));
                        scMo.setcFreebySh((BigDecimal) m.get("freeby_sh"));
                        scMo.setcFreebyOi((BigDecimal) m.get("freeby_oi"));
                        scMo.setcFreebyBo((BigDecimal) m.get("freeby_bo"));
                        scMo.setcFreebyIsIn((String) m.get("freeby_is_in"));
                        scMo.setcFreebyIsSssc((String) m.get("freeby_is_sssc"));
                        scMo.setcFreebyIsSd((String) m.get("freeby_is_sd"));
                        scMo.setcFreebyIsOil((String) m.get("freeby_is_oil"));
                        scMo.setcFreebyIsPre((String) m.get("freeby_is_pre"));
                        scMo.setcFreebyIsTi((String) m.get("freeby_is_ti"));
                        scMo.setcPurTaxSi((String) m.get("pur_tax_si"));
                        scMo.setcBoPriceTi((BigDecimal) m.get("bo_price_ti"));
                        scMo.setcExInPriceTi((BigDecimal) m.get("ex_in_price_ti"));
                        scMo.setcLicFee((BigDecimal) m.get("lic_fee"));
                        scMo.setcOthFee((BigDecimal) m.get("oth_fee"));
                        scMo.setcFinFee((BigDecimal) m.get("fin_fee"));
                        scMo.setCompareResult("02");
                        break;
                    } // 不匹配
                }
                if (i == imList.size()) { // 如果在imList中找不到匹配的VIN，说明此车没有包含在导入的EXCEL中，作为新记录入库
                    TSalesConsultantCheckModel mo = new TSalesConsultantCheckModel();
                    mo.setCustomTel((String) m.get("custom_tel"));
                    mo.setSc((String) m.get("sc"));
                    mo.setVin((String) m.get("vin"));
                    mo.setIntentCar((String) m.get("intent_car"));
                    mo.setImportBatch(batch);
                    mo.setPermit(permit);
                    mo.setSalesStatus((String) m.get("sales_status")); // 特别的，此时补一个新车销售状态
                    mo.setcIoDt((java.sql.Date) m.get("io_dt"));
                    mo.setcOrderCusName((String) m.get("order_cus_name"));
                    mo.setcIoAmount((BigDecimal) m.get("io_amount"));
                    mo.setcContractSignedDt((java.sql.Date) m.get("contract_signed_dt"));
                    mo.setcInvoiceDt((java.sql.Date) m.get("invoice_dt"));
                    mo.setcInvoiceUname((String) m.get("invoice_uname"));
                    mo.setcDeliverDt((java.sql.Date) m.get("deliver_dt"));
                    mo.setcPriceTi((BigDecimal) m.get("price_ti"));
                    mo.setcFreebyIn((BigDecimal) m.get("freeby_in"));
                    mo.setcFreebySubInt((BigDecimal) m.get("freeby_sub_int"));
                    mo.setcFreebyA(m.get("freeby_a") == null ? null : ((BigInteger) m.get("freeby_a")).intValue());
                    mo.setcFreebyB(m.get("freeby_b") == null ? null : ((BigInteger) m.get("freeby_b")).intValue());
                    mo.setcFreebySi((String) m.get("freeby_si"));
                    mo.setcFreebyBl((String) m.get("freeby_bl"));
                    mo.setcFreebySh((BigDecimal) m.get("freeby_sh"));
                    mo.setcFreebyOi((BigDecimal) m.get("freeby_oi"));
                    mo.setcFreebyBo((BigDecimal) m.get("freeby_bo"));
                    mo.setcFreebyIsIn((String) m.get("freeby_is_in"));
                    mo.setcFreebyIsSssc((String) m.get("freeby_is_sssc"));
                    mo.setcFreebyIsSd((String) m.get("freeby_is_sd"));
                    mo.setcFreebyIsOil((String) m.get("freeby_is_oil"));
                    mo.setcFreebyIsPre((String) m.get("freeby_is_pre"));
                    mo.setcFreebyIsTi((String) m.get("freeby_is_ti"));
                    mo.setcPurTaxSi((String) m.get("pur_tax_si"));
                    mo.setcBoPriceTi((BigDecimal) m.get("bo_price_ti"));
                    mo.setcExInPriceTi((BigDecimal) m.get("ex_in_price_ti"));
                    mo.setcLicFee((BigDecimal) m.get("lic_fee"));
                    mo.setcOthFee((BigDecimal) m.get("oth_fee"));
                    mo.setcFinFee((BigDecimal) m.get("fin_fee"));
                    mo.setCompareResult("03");
                    tmpList.add(mo);
                }
                // imList中存在，sysList中不存在的数据，已经在库中了，并且相应的c字段（比较字段）都为null，不需要额外处理就可以进入下阶段
            }
            // 将tmpList中的数据添加到imList中
            imList.addAll(tmpList);
            logger.info("scInputDataCheck：" + JsonObjectUtils.writeAsJson(tmpList));

            // 2.处理imList中的分数计算
            for (TSalesConsultantCheckModel mo : imList) {
                BigDecimal zero = new BigDecimal("0.0"); // 销售行政字段相同时扣0分
                BigDecimal de = new BigDecimal("-3.0"); // 字段不同扣3分
                BigDecimal tolerance = new BigDecimal("0.5"); // 数值类型比较时，差值0.5内可以认为相同
                DecimalFormat format = new DecimalFormat("#");
                // EXCEL中未填入的数据被解析成NULL存入数据库，如果两个值同时为NULL，不扣分，两值相等也不扣分
                if ("02".equals(mo.getCompareResult())) {
                    if ((mo.getIoDt() == null && mo.getcIoDt() == null) || (mo.getIoDt() != null && mo.getcIoDt() != null && mo.getIoDt().equals(mo.getcIoDt()))) {
                        mo.setdIoDt(zero);
                    } else {
                        mo.setdIoDt(de);
                    }
                    if ((mo.getOrderCusName() == null && mo.getcOrderCusName() == null) || (mo.getOrderCusName() != null && mo.getcOrderCusName() != null && mo.getOrderCusName().equals(mo.getcOrderCusName()))) {
                        mo.setdOrderCusName(zero);
                    } else {
                        mo.setdOrderCusName(de);
                    }
                    if ((mo.getIoAmount() == null && mo.getcIoAmount() == null) || (mo.getIoAmount() != null && mo.getcIoAmount() != null && mo.getIoAmount().subtract(mo.getcIoAmount()).abs().compareTo(tolerance) <= 0)
                            || (mo.getIoAmount() == null && mo.getcIoAmount() != null && "0".equals(format.format(mo.getcIoAmount())))
                            || (mo.getcIoAmount() == null && mo.getIoAmount() != null && "0".equals(format.format(mo.getIoAmount()))) ) {
                        mo.setdIoAmount(zero);
                    } else {
                        mo.setdIoAmount(de);
                    }
                    if ((mo.getContractSignedDt() == null && mo.getcContractSignedDt() == null) || (mo.getContractSignedDt() != null && mo.getcContractSignedDt() != null && mo.getContractSignedDt().equals(mo.getcContractSignedDt()))) {
                        mo.setdContractSignedDt(zero);
                    } else {
                        mo.setdContractSignedDt(de);
                    }
                    if ((mo.getInvoiceDt() == null && mo.getcInvoiceDt() == null) || (mo.getInvoiceDt() != null && mo.getcInvoiceDt() != null && mo.getInvoiceDt().equals(mo.getcInvoiceDt()))) {
                        mo.setdInvoiceDt(zero);
                    } else {
                        mo.setdInvoiceDt(de);
                    }
                    if ((mo.getInvoiceUname() == null && mo.getcInvoiceUname() == null) || (mo.getInvoiceUname() != null && mo.getcInvoiceUname() != null && mo.getInvoiceUname().equals(mo.getcInvoiceUname()))) {
                        mo.setdInvoiceUname(zero);
                    } else {
                        mo.setdInvoiceUname(de);
                    }
                    if ((mo.getDeliverDt() == null && mo.getcDeliverDt() == null) || (mo.getDeliverDt() != null && mo.getcDeliverDt() != null && mo.getDeliverDt().equals(mo.getcDeliverDt()))) {
                        mo.setdDeliverDt(zero);
                    } else {
                        mo.setdDeliverDt(de);
                    }
                    if ((mo.getPriceTi() == null && mo.getcPriceTi() == null) || (mo.getPriceTi() != null && mo.getcPriceTi() != null && mo.getPriceTi().subtract(mo.getcPriceTi()).abs().compareTo(tolerance) <= 0)
                            || (mo.getPriceTi() == null && mo.getcPriceTi() != null && "0".equals(format.format(mo.getcPriceTi())))
                            || (mo.getcPriceTi() == null && mo.getPriceTi() != null && "0".equals(format.format(mo.getPriceTi())))) {
                        mo.setdPriceTi(zero);
                    } else {
                        mo.setdPriceTi(de);
                    }
                    if ((mo.getFreebyIn() == null && mo.getcFreebyIn() == null) || (mo.getFreebyIn() != null && mo.getcFreebyIn() != null && mo.getFreebyIn().subtract(mo.getcFreebyIn()).abs().compareTo(tolerance) <= 0)
                            || (mo.getFreebyIn() == null && mo.getcFreebyIn() != null && "0".equals(format.format(mo.getcFreebyIn())))
                            || (mo.getcFreebyIn() == null && mo.getFreebyIn() != null && "0".equals(format.format(mo.getFreebyIn())))) {
                        mo.setdFreebyIn(zero);
                    } else {
                        mo.setdFreebyIn(de);
                    }
                    if ((mo.getFreebySubInt() == null && mo.getcFreebySubInt() == null) || (mo.getFreebySubInt() != null && mo.getcFreebySubInt() != null && mo.getFreebySubInt().subtract(mo.getcFreebySubInt()).abs().compareTo(tolerance) <= 0)
                            || (mo.getFreebySubInt() == null && mo.getcFreebySubInt() != null && "0".equals(format.format(mo.getcFreebySubInt())))
                            || (mo.getcFreebySubInt() == null && mo.getFreebySubInt() != null && "0".equals(format.format(mo.getFreebySubInt())))) {
                        mo.setdFreebySubInt(zero);
                    } else {
                        mo.setdFreebySubInt(de);
                    }
                    if ((mo.getFreebyA() == null && mo.getcFreebyA() == null) || (mo.getFreebyA() != null && mo.getcFreebyA() != null && mo.getFreebyA().equals(mo.getcFreebyA()))) {
                        mo.setdFreebyA(zero);
                    } else {
                        mo.setdFreebyA(de);
                    }
                    if ((mo.getFreebyB() == null && mo.getcFreebyB() == null) || (mo.getFreebyB() != null && mo.getcFreebyB() != null && mo.getFreebyB().equals(mo.getcFreebyB()))) {
                        mo.setdFreebyB(zero);
                    } else {
                        mo.setdFreebyB(de);
                    }
                    if ((mo.getFreebySi() == null && mo.getcFreebySi() == null) || (mo.getFreebySi() != null && mo.getcFreebySi() != null && mo.getFreebySi().equals(mo.getcFreebySi()))) {
                        mo.setdFreebySi(zero);
                    } else {
                        mo.setdFreebySi(de);
                    }
                    if ((mo.getFreebyBl() == null && mo.getcFreebyBl() == null) || (mo.getFreebyBl() != null && mo.getcFreebyBl() != null && mo.getFreebyBl().equals(mo.getcFreebyBl()))) {
                        mo.setdFreebyBl(zero);
                    } else {
                        mo.setdFreebyBl(de);
                    }
                    if ((mo.getFreebySh() == null && mo.getcFreebySh() == null) || (mo.getFreebySh() != null && mo.getcFreebySh() != null && mo.getFreebySh().subtract(mo.getcFreebySh()).abs().compareTo(tolerance) <= 0)
                            || (mo.getFreebySh() == null && mo.getcFreebySh() != null && "0".equals(format.format(mo.getcFreebySh())))
                            || (mo.getcFreebySh() == null && mo.getFreebySh() != null && "0".equals(format.format(mo.getFreebySh())))) {
                        mo.setdFreebySh(zero);
                    } else {
                        mo.setdFreebySh(de);
                    }
                    if ((mo.getFreebyOi() == null && mo.getcFreebyOi() == null) || (mo.getFreebyOi() != null && mo.getcFreebyOi() != null && mo.getFreebyOi().subtract(mo.getcFreebyOi()).abs().compareTo(tolerance) <= 0)
                            || (mo.getFreebyOi() == null && mo.getcFreebyOi() != null && "0".equals(format.format(mo.getcFreebyOi())))
                            || (mo.getcFreebyOi() == null && mo.getFreebyOi() != null && "0".equals(format.format(mo.getFreebyOi())))) {
                        mo.setdFreebyOi(zero);
                    } else {
                        mo.setdFreebyOi(de);
                    }
                    if ((mo.getFreebyBo() == null && mo.getcFreebyBo() == null) || (mo.getFreebyBo() != null && mo.getcFreebyBo() != null && mo.getFreebyBo().subtract(mo.getcFreebyBo()).abs().compareTo(tolerance) <= 0)
                            || (mo.getFreebyBo() == null && mo.getcFreebyBo() != null && "0".equals(format.format(mo.getcFreebyBo())))
                            || (mo.getcFreebyBo() == null && mo.getFreebyBo() != null && "0".equals(format.format(mo.getFreebyBo())))) {
                        mo.setdFreebyBo(zero);
                    } else {
                        mo.setdFreebyBo(de);
                    }
                    if (mo.getFreebyIsIn().equals(mo.getcFreebyIsIn())) {
                        mo.setdFreebyIsIn(zero);
                    } else {
                        mo.setdFreebyIsIn(de);
                    }
                    if (mo.getFreebyIsSssc().equals(mo.getcFreebyIsSssc())) {
                        mo.setdFreebyIsSssc(zero);
                    } else {
                        mo.setdFreebyIsSssc(de);
                    }
                    if (mo.getFreebyIsSd().equals(mo.getcFreebyIsSd())) {
                        mo.setdFreebyIsSd(zero);
                    } else {
                        mo.setdFreebyIsSd(de);
                    }
                    if (mo.getFreebyIsOil().equals(mo.getcFreebyIsOil())) {
                        mo.setdFreebyIsOil(zero);
                    } else {
                        mo.setdFreebyIsOil(de);
                    }
                    if (mo.getFreebyIsPre().equals(mo.getcFreebyIsPre())) {
                        mo.setdFreebyIsPre(zero);
                    } else {
                        mo.setdFreebyIsPre(de);
                    }
                    if (mo.getFreebyIsTi().equals(mo.getcFreebyIsTi())) {
                        mo.setdFreebyIsTi(zero);
                    } else {
                        mo.setdFreebyIsTi(de);
                    }
                    if (mo.getPurTaxSi().equals(mo.getcPurTaxSi())) {
                        mo.setdPurTaxSi(zero);
                    } else {
                        mo.setdPurTaxSi(de);
                    }
                    if ((mo.getBoPriceTi() == null && mo.getcBoPriceTi() == null) || (mo.getBoPriceTi() != null && mo.getcBoPriceTi() != null && mo.getBoPriceTi().subtract(mo.getcBoPriceTi()).abs().compareTo(tolerance) <= 0)
                            || (mo.getBoPriceTi() == null && mo.getcBoPriceTi() != null && "0".equals(format.format(mo.getcBoPriceTi())))
                            || (mo.getcBoPriceTi() == null && mo.getBoPriceTi() != null && "0".equals(format.format(mo.getBoPriceTi())))) {
                        mo.setdBoPriceTi(zero);
                    } else {
                        mo.setdBoPriceTi(de);
                    }
                    if ((mo.getExInPriceTi() == null && mo.getcExInPriceTi() == null) || (mo.getExInPriceTi() != null && mo.getcExInPriceTi() != null && mo.getExInPriceTi().subtract(mo.getcExInPriceTi()).abs().compareTo(tolerance) <= 0)
                            || (mo.getExInPriceTi() == null && mo.getcExInPriceTi() != null && "0".equals(format.format(mo.getcExInPriceTi())))
                            || (mo.getcExInPriceTi() == null && mo.getExInPriceTi() != null && "0".equals(format.format(mo.getExInPriceTi()))) ) {
                        mo.setdExInPriceTi(zero);
                    } else {
                        mo.setdExInPriceTi(de);
                    }
                    if ((mo.getLicFee() == null && mo.getcLicFee() == null) || (mo.getLicFee() != null && mo.getcLicFee() != null && mo.getLicFee().subtract(mo.getcLicFee()).abs().compareTo(tolerance) <= 0)
                            || (mo.getLicFee() == null && mo.getcLicFee() != null && "0".equals(format.format(mo.getcLicFee())))
                            || (mo.getcLicFee() == null && mo.getLicFee() != null && "0".equals(format.format(mo.getLicFee()))) ) {
                        mo.setdLicFee(zero);
                    } else {
                        mo.setdLicFee(de);
                    }
                    if ((mo.getOthFee() == null && mo.getcOthFee() == null) || (mo.getOthFee() != null && mo.getcOthFee() != null && mo.getOthFee().subtract(mo.getcOthFee()).abs().compareTo(tolerance) <= 0)
                            || (mo.getOthFee() == null && mo.getcOthFee() != null && "0".equals(format.format(mo.getcOthFee())))
                            || (mo.getcOthFee() == null && mo.getOthFee() != null && "0".equals(format.format(mo.getOthFee())))  ) {
                        mo.setdOthFee(zero);
                    } else {
                        mo.setdOthFee(de);
                    }
                    if ((mo.getFinFee() == null && mo.getcFinFee() == null) || (mo.getFinFee() != null && mo.getcFinFee() != null && mo.getFinFee().subtract(mo.getcFinFee()).abs().compareTo(tolerance) <= 0)
                            || (mo.getFinFee() == null && mo.getcFinFee() != null && "0".equals(format.format(mo.getcFinFee())))
                            || (mo.getcFinFee() == null && mo.getFinFee() != null && "0".equals(format.format(mo.getFinFee()))) ) {
                        mo.setdFinFee(zero);
                    } else {
                        mo.setdFinFee(de);
                    }
                }
                if ("00".equals(mo.getCompareResult())) { // 此处还没有更新比较结果的数据，就是导入有，实际无的数据
                    mo.setCompareResult("01");
                }
            }
            // 将imList以外的销售顾问，每人添加一条满分数据到库里
            // 无论imList是否为空，都满足需求（不存在于当天导入excel中的销售顾问，每人给一条满分数据）
            // 先取出该许可下所有销售顾问
            List scList = userRep.getAllScNameCnByPermit(permit);
            Set<String> scAll = new HashSet<>();
            Set<String> scIm = new HashSet<>();
            for (Object o : scList) {
                String scName = (String) o;
                scAll.add(scName);
            }
            for (TSalesConsultantCheckModel mo : imList) {
                scIm.add(mo.getSc());
            }
            scAll.removeAll(scIm);  // 找到没数据的销售顾问
            for (String name : scAll) {
                TSalesConsultantCheckModel mo = new TSalesConsultantCheckModel();
                mo.setImportBatch(batch);
                mo.setPermit(permit);
                mo.setSc(name);
                mo.setVin("");
                mo.setSalesStatus("意向金");
                mo.setTotalScore(new BigDecimal("100"));
                mo.setCompareResult("04");
                mo.setImportUser(user);  // 插入用户
                mo.setImportTime(new Timestamp(new Date().getTime())); // 插入时间
                scChRep.save(mo);
                logger.warn("单日批次销售顾问数据为空, name={}, 批次={}", name, batch);
            }
            // 3.计算总分
            for (TSalesConsultantCheckModel mo : imList) {
                BigDecimal total = new BigDecimal("100"); // 百分制评分
                if ("02".equals(mo.getCompareResult())) { // 匹配
                    total = total.add(mo.getdIoDt());
                    total = total.add(mo.getdOrderCusName());
                    total = total.add(mo.getdIoAmount());
                    total = total.add(mo.getdContractSignedDt());
                    total = total.add(mo.getdInvoiceDt());
                    total = total.add(mo.getdInvoiceUname());
                    total = total.add(mo.getdDeliverDt());
                    total = total.add(mo.getdPriceTi());
                    total = total.add(mo.getdFreebyIn());
                    total = total.add(mo.getdFreebySubInt());
                    total = total.add(mo.getdFreebyA());
                    total = total.add(mo.getdFreebyB());
                    total = total.add(mo.getdFreebySi());
                    total = total.add(mo.getdFreebyBl());
                    total = total.add(mo.getdFreebySh());
                    total = total.add(mo.getdFreebyOi());
                    total = total.add(mo.getdFreebyBo());
                    total = total.add(mo.getdFreebyIsIn());
                    total = total.add(mo.getdFreebyIsSssc());
                    total = total.add(mo.getdFreebyIsSd());
                    total = total.add(mo.getdFreebyIsOil());
                    total = total.add(mo.getdFreebyIsPre());
                    total = total.add(mo.getdFreebyIsTi());
                    total = total.add(mo.getdPurTaxSi());
                    total = total.add(mo.getdBoPriceTi());
                    total = total.add(mo.getdExInPriceTi());
                    total = total.add(mo.getdLicFee());
                    total = total.add(mo.getdOthFee());
                    total = total.add(mo.getdFinFee());
                } else { // 不匹配，给0分
                    total = new BigDecimal("0");
                }
                // 写入导入用户
                mo.setImportUser(user);
                mo.setImportTime(new Timestamp(new Date().getTime())); // 插入时间
                mo.setTotalScore(total);
            }
            // 4.保存impList
            scChRep.save(imList);
        } catch (Exception e) {
            result.setResult("false");
            result.setMessage("评分出现异常");
            logger.error("rankSalesConsultantData出错,Dealer={}",permit,e);
        }
        return result;
    }



    // 对于金融数据评分
    private ResultData rankFinanceData(String permit,String user){
        ResultData reData = new ResultData();

        //导入批次
        String importBatch = importBatchSetting();

        try {

            //更新实际数据
//            checkFinanceSql.updateFinanceCheckData(permit,importBatch);
//            checkFinanceSql.insertFinanceCheckData(permit,importBatch);
            //今日所有金融数据列表
//            List<TFinanceCheckModel> financeList = financeCheckRepository.findFinanceByImportBatchAndPermit(importBatch,permit);

            // 导入的数据
            List<TFinanceCheckModel> financeList = checkFinanceSql.selectFinanceCheckData(permit,importBatch);
            // 数据库数据
            List sysList = checkFinanceSql.selectDBFinanceCheckData(permit,importBatch);
            // 存放数据库有，导入没有的数据
            List<TFinanceCheckModel> tmpList = new ArrayList<>();

            for (Object o : sysList) {
                Map m = (Map) o;
                int i = 0;
                for (; i < financeList.size(); i++) {
                    TFinanceCheckModel model = financeList.get(i);
                    // vin匹配则更新
                    if (m.get("vin").equals(model.getVin())) {
                        model.setDbLoanCustName(getString("db_loan_cust_name",m));
                        model.setDbLoanApprovalDate(getDate("db_loan_approval_date",m));
                        model.setDbFinanceCompany(getString("db_finance_company",m));
                        model.setDbDepositPayment(getBigDecimal("db_deposit_payment",m));
                        model.setDbDownPayment(getBigDecimal("db_down_payment",m));
                        model.setDbLoanPayment(getBigDecimal("db_loan_payment",m));
                        model.setDbLoanTerm(getInteger("db_loan_term",m));
                        model.setDbYearlyRate(getBigDecimal("db_yearly_rate",m));
                        model.setDbBmbsDiscount(getBigDecimal("db_BMBS_discount",m));
                        model.setDbDealerDiscount(getBigDecimal("db_dealer_discount",m));
                        model.setDbAccountDate(getDate("db_account_date",m));
                        model.setDbFinanceFee(getBigDecimal("db_finance_fee",m));
                        model.setDbFinanceServiceFee(getBigDecimal("db_finance_service_fee",m));
                        model.setCompareResult("02");
                        break;
                    }
                }
                // 不存在则插入
                if (i == financeList.size()){
                    TFinanceCheckModel model = new TFinanceCheckModel();
                    model.setImportBatch(importBatch);
                    model.setPermit(permit);
                    model.setVin((String) m.get("vin"));
                    model.setEvaluationUser((String) m.get("evaluation_user"));
                    model.setDbLoanCustName(getString("db_loan_cust_name",m));
                    model.setDbLoanApprovalDate(getDate("db_loan_approval_date",m));
                    model.setDbFinanceCompany(getString("db_finance_company",m));
                    model.setDbDepositPayment(getBigDecimal("db_deposit_payment",m));
                    model.setDbDownPayment(getBigDecimal("db_down_payment",m));
                    model.setDbLoanPayment(getBigDecimal("db_loan_payment",m));
                    model.setDbLoanTerm(getInteger("db_loan_term",m));
                    model.setDbYearlyRate(getBigDecimal("db_yearly_rate",m));
                    model.setDbBmbsDiscount(getBigDecimal("db_BMBS_discount",m));
                    model.setDbDealerDiscount(getBigDecimal("db_dealer_discount",m));
                    model.setDbAccountDate(getDate("db_account_date",m));
                    model.setDbFinanceFee(getBigDecimal("db_finance_fee",m));
                    model.setDbFinanceServiceFee(getBigDecimal("db_finance_service_fee",m));
                    model.setCompareResult("03");
                    tmpList.add(model);
                }
            }
            financeList.addAll(tmpList);


            if (!financeList.isEmpty()) {
                //循环评分
                for (int i = 0; i < financeList.size(); i++) {

                    TFinanceCheckModel model = financeList.get(i);

                    if (Strings.isNullOrEmpty(model.getDbFinanceCompany())
                            && Strings.isNullOrEmpty(model.getDbLoanCustName())){
                        // 导入有，实际无
                        model.setCompareResult("01");
                        model.setTotalScore(new BigDecimal(0));
                    } else {
                        model.setDeductionLoanCustName(getDeductionValue(model.getImportLoanCustName(), model.getDbLoanCustName(), DEFAULT_Deduction_Finance));
                        model.setDeductionLoanApprovalDate(getDeductionValue(model.getImportLoanApprovalDate(), model.getDbLoanApprovalDate(), DEFAULT_Deduction_Finance));
                        model.setDeductionFinanceCompany(getDeductionValue(model.getImportFinanceCompany(), model.getDbFinanceCompany(), DEFAULT_Deduction_Finance));
                        model.setDeductionDepositPayment(getDeductionValue(model.getImportDepositPayment(), model.getDbDepositPayment(), DEFAULT_Deduction_Finance));
                        model.setDeductionDownPayment(getDeductionValue(model.getImportDownPayment(), model.getDbDownPayment(), DEFAULT_Deduction_Finance));
                        model.setDeductionLoanPayment(getDeductionValue(model.getImportLoanPayment(), model.getDbLoanPayment(), DEFAULT_Deduction_Finance));
                        model.setDeductionLoanTerm(getDeductionValue(model.getImportLoanTerm(), model.getDbLoanTerm(), DEFAULT_Deduction_Finance));
                        model.setDeductionYearlyRate(getDeductionYearlyRate(model.getImportYearlyRate(), model.getDbYearlyRate(), DEFAULT_Deduction_Finance));
                        model.setDeductionBmbsDiscount(getDeductionValue(model.getImportBmbsDiscount(), model.getDbBmbsDiscount(), DEFAULT_Deduction_Finance));
                        model.setDeductionDealerDiscount(getDeductionValue(model.getImportDealerDiscount(), model.getDbDealerDiscount(), DEFAULT_Deduction_Finance));
                        model.setDeductionAccountDate(getDeductionValue(model.getImportAccountDate(), model.getDbAccountDate(), DEFAULT_Deduction_Finance));
                        model.setDeductionFinanceFee(getDeductionValue(model.getImportFinanceFee(), model.getDbFinanceFee(), DEFAULT_Deduction_Finance));
                        model.setDeductionFinanceServiceFee(getDeductionValue(model.getImportFinanceServiceFee(), model.getDbFinanceServiceFee(), DEFAULT_Deduction_Finance));

                        // 计算总分
                        BigDecimal score = new BigDecimal(100)
                                .add(model.getDeductionLoanCustName())
                                .add(model.getDeductionLoanApprovalDate())
                                .add(model.getDeductionFinanceCompany())
                                .add(model.getDeductionDepositPayment())
                                .add(model.getDeductionDownPayment())
                                .add(model.getDeductionLoanPayment())
                                .add(model.getDeductionLoanTerm())
                                .add(model.getDeductionYearlyRate())
                                .add(model.getDeductionBmbsDiscount())
                                .add(model.getDeductionDealerDiscount())
                                .add(model.getDeductionAccountDate())
                                .add(model.getDeductionFinanceFee())
                                .add(model.getDeductionFinanceServiceFee());
                        // 需求变更，不存在底分，转为0分
                        if (new BigDecimal(DEFAULT_Basic_Finance).equals(score)){
                            score = new BigDecimal(0);
                        }
                        model.setTotalScore(score);
                    }
                    // 写入导入用户
                    model.setImportUser(user);
                    model.setImportTime(new Timestamp(new Date().getTime()));

                    // 更新model
                    financeList.set(i, model);
                }
                // 入库
                financeCheckRepository.save(financeList);
            } else {
                // 导入没有，数据库也没有，则为100分
                TFinanceCheckModel empty = new TFinanceCheckModel();
                empty.setImportBatch(importBatch);
                empty.setPermit(permit);
                empty.setVin("");
                empty.setTotalScore(new BigDecimal(100));
                empty.setCompareResult("04");
                empty.setImportUser(user);
                empty.setImportTime(new Timestamp(new Date().getTime()));
                financeCheckRepository.save(empty);
                logger.warn("单日批次金融数据为空.Dealer={},批次={}",permit,importBatch);
            }
        } catch (Exception e){
            logger.error("rankFinanceData出错,Dealer={},批次={}",permit,importBatch,e);
            reData.setResult("false");
            reData.setMessage("rankFinanceData出错");
        }
        return reData;
    }

    // 对于保险数据评分
    private ResultData rankInsuranceData(String permit,String user) {
        ResultData reData = new ResultData();

        //导入批次
        String importBatch = importBatchSetting();

        try {

            //更新实际数据
//            checkInsuranceSql.updateInsuranceCheckData(permit,importBatch);
//            checkInsuranceSql.insertInsuranceCheckData(permit,importBatch);
            //今日所有金融数据列表
//            List<TInsuranceCheckModel> insuranceList = insuranceCheckRepository.findInsuranceByImportBatchAndPermit(importBatch,permit);

            // 导入的数据
            List<TInsuranceCheckModel> insuranceList = checkInsuranceSql.selectInsuranceCheckData(permit,importBatch);
            // 数据库数据
            List sysList = checkInsuranceSql.selectDBInsuranceCheckData(permit,importBatch);
            // 存放数据库有，导入没有的数据
            List<TInsuranceCheckModel> tmpList = new ArrayList<>();

            for (Object o : sysList) {
                Map m = (Map) o;
                int i = 0;
                for (; i < insuranceList.size(); i++) {
                    TInsuranceCheckModel model = insuranceList.get(i);
                    // vin匹配则更新
                    if (m.get("vin").equals(model.getVin())) {
                        model.setDbInsuredCustName(getString("db_insured_cust_name",m));
                        model.setDbTciPolicyNum(getString("db_TCI_policy_num",m));
                        model.setDbVciPolicyNum(getString("db_VCI_policy_num",m));
                        model.setDbTciInvoiceDate(getDate("db_TCI_invoice_date",m));
                        model.setDbVciInvoiceDate(getDate("db_VCI_invoice_date",m));
                        model.setDbTciEffectDate(getDate("db_TCI_effect_date",m));
                        model.setDbTciExpirationDate(getDate("db_TCI_expiration_date",m));
                        model.setDbVciEffectDate(getDate("db_VCI_effect_date",m));
                        model.setDbVciExpirationDate(getDate("db_VCI_expiration_date",m));
                        model.setDbTciCompany(getString("db_TCI_company",m));
                        model.setDbVciCompany(getString("db_VCI_company",m));
                        model.setDbTciPrice(getBigDecimal("db_TCI_price",m));
                        model.setDbVciPrice(getBigDecimal("db_VCI_price",m));
                        model.setDbTciFee(getBigDecimal("db_TCI_fee",m));
                        model.setDbVciFee(getBigDecimal("db_VCI_fee",m));
                        model.setCompareResult("02");
                        break;
                    }
                }
                // 不存在则插入
                if (i == insuranceList.size()){
                    TInsuranceCheckModel model = new TInsuranceCheckModel();
                    model.setImportBatch(importBatch);
                    model.setPermit(permit);
                    model.setVin(getString("vin",m));
                    model.setEvaluationUser((String) m.get("evaluation_user"));
                    model.setDbInsuredCustName(getString("db_insured_cust_name",m));
                    model.setDbTciPolicyNum(getString("db_TCI_policy_num",m));
                    model.setDbVciPolicyNum(getString("db_VCI_policy_num",m));
                    model.setDbTciInvoiceDate(getDate("db_TCI_invoice_date",m));
                    model.setDbVciInvoiceDate(getDate("db_VCI_invoice_date",m));
                    model.setDbTciEffectDate(getDate("db_TCI_effect_date",m));
                    model.setDbTciExpirationDate(getDate("db_TCI_expiration_date",m));
                    model.setDbVciEffectDate(getDate("db_VCI_effect_date",m));
                    model.setDbVciExpirationDate(getDate("db_VCI_expiration_date",m));
                    model.setDbTciCompany(getString("db_TCI_company",m));
                    model.setDbVciCompany(getString("db_VCI_company",m));
                    model.setDbTciPrice(getBigDecimal("db_TCI_price",m));
                    model.setDbVciPrice(getBigDecimal("db_VCI_price",m));
                    model.setDbTciFee(getBigDecimal("db_TCI_fee",m));
                    model.setDbVciFee(getBigDecimal("db_VCI_fee",m));
                    model.setCompareResult("03");
                    tmpList.add(model);
                }
            }
            insuranceList.addAll(tmpList);


            if (!insuranceList.isEmpty()) {
                //循环评分
                for (int i = 0; i < insuranceList.size(); i++) {

                    TInsuranceCheckModel model = insuranceList.get(i);

                    if (Strings.isNullOrEmpty(model.getDbTciCompany())
                            && Strings.isNullOrEmpty(model.getDbInsuredCustName())){
                        // 导入有，实际无
                        model.setCompareResult("01");
                        model.setTotalScore(new BigDecimal(0));
                    } else {
                        model.setDeductionInsuredCustName(getDeductionValue(model.getImportInsuredCustName(), model.getDbInsuredCustName(), DEFAULT_Deduction_Insurance));
                        model.setDeductionTciPolicyNum(getDeductionValue(model.getImportTciPolicyNum(), model.getDbTciPolicyNum(), DEFAULT_Deduction_Insurance));
                        model.setDeductionVciPolicyNum(getDeductionValue(model.getImportVciPolicyNum(), model.getDbVciPolicyNum(), DEFAULT_Deduction_Insurance));
                        model.setDeductionTciInvoiceDate(getDeductionValue(model.getImportTciInvoiceDate(), model.getDbTciInvoiceDate(), DEFAULT_Deduction_Insurance));
                        model.setDeductionVciInvoiceDate(getDeductionValue(model.getImportVciInvoiceDate(), model.getDbVciInvoiceDate(), DEFAULT_Deduction_Insurance));
                        model.setDeductionTciEffectDate(getDeductionValue(model.getImportTciEffectDate(), model.getDbTciEffectDate(), DEFAULT_Deduction_Insurance));
                        model.setDeductionTciExpirationDate(getDeductionValue(model.getImportTciExpirationDate(), model.getDbTciExpirationDate(), DEFAULT_Deduction_Insurance));
                        model.setDeductionVciEffectDate(getDeductionValue(model.getImportVciEffectDate(), model.getDbVciEffectDate(), DEFAULT_Deduction_Insurance));
                        model.setDeductionVciExpirationDate(getDeductionValue(model.getImportVciExpirationDate(), model.getDbVciExpirationDate(), DEFAULT_Deduction_Insurance));
                        model.setDeductionTciCompany(getDeductionValue(model.getImportTciCompany(), model.getDbTciCompany(), DEFAULT_Deduction_Insurance));
                        model.setDeductionVciCompany(getDeductionValue(model.getImportVciCompany(), model.getDbVciCompany(), DEFAULT_Deduction_Insurance));
                        model.setDeductionTciPrice(getDeductionValue(model.getImportTciPrice(), model.getDbTciPrice(), DEFAULT_Deduction_Insurance));
                        model.setDeductionVciPrice(getDeductionValue(model.getImportVciPrice(), model.getDbVciPrice(), DEFAULT_Deduction_Insurance));
                        model.setDeductionTciFee(getDeductionValue(model.getImportTciFee(), model.getDbTciFee(), DEFAULT_Deduction_Insurance));
                        model.setDeductionVciFee(getDeductionValue(model.getImportVciFee(), model.getDbVciFee(), DEFAULT_Deduction_Insurance));



                        // 计算总分
                        BigDecimal score = new BigDecimal(100)
                                .add(model.getDeductionInsuredCustName())
                                .add(model.getDeductionTciPolicyNum())
                                .add(model.getDeductionVciPolicyNum())
                                .add(model.getDeductionTciInvoiceDate())
                                .add(model.getDeductionVciInvoiceDate())
                                .add(model.getDeductionTciEffectDate())
                                .add(model.getDeductionTciExpirationDate())
                                .add(model.getDeductionVciEffectDate())
                                .add(model.getDeductionVciExpirationDate())
                                .add(model.getDeductionTciCompany())
                                .add(model.getDeductionVciCompany())
                                .add(model.getDeductionTciPrice())
                                .add(model.getDeductionVciPrice())
                                .add(model.getDeductionTciFee())
                                .add(model.getDeductionVciFee());
                        // 需求变更，不存在底分，转为0分
                        if (new BigDecimal(DEFAULT_Basic_Insurance).equals(score)){
                            score = new BigDecimal(0);
                        }
                        model.setTotalScore(score);
                    }
                    // 写入导入用户
                    model.setImportUser(user);
                    model.setImportTime(new Timestamp(new Date().getTime()));
                    // 更新model
                    insuranceList.set(i, model);
                }
                // 入库
                insuranceCheckRepository.save(insuranceList);
            } else {
                // 导入没有，数据库也没有，则为100分
                TInsuranceCheckModel empty = new TInsuranceCheckModel();
                empty.setImportBatch(importBatch);
                empty.setPermit(permit);
                empty.setVin("");
                empty.setTotalScore(new BigDecimal(100));
                empty.setCompareResult("04");
                empty.setImportUser(user);
                empty.setImportTime(new Timestamp(new Date().getTime()));
                insuranceCheckRepository.save(empty);
                logger.warn("单日批次保险数据为空.Dealer={},批次={}",permit,importBatch);
            }
        } catch (Exception e){
            logger.error("rankInsuranceData出错,Dealer={},批次={}",permit,importBatch,e);
            reData.setResult("false");
            reData.setMessage("rankInsuranceData出错");
        }
        return reData;
    }

    @Override
    public ResultData getContrastResultOfFinance(RequestBase req,String permit) throws Exception{
        ResultData result = new ResultData("true",null,null);
        String importBatch = importBatchSetting(); //导入批次
        PageData<Map<String, Object>> pageData =  financeCheckRepositorySql.getContrastResultOfFinance(req,importBatch,permit);
        //匹配状态设置
        setMatchStatus(pageData);
        result.setPageData(pageData);
        return result;
    }

    @Override
    public ResultData getContrastResultOfInsurance(RequestBase req, String permit) throws Exception {
        {
            ResultData result = new ResultData("true",null,null);
            String importBatch = importBatchSetting(); //导入批次
            PageData<Map<String, Object>> pageData =  insuranceCheckRepositorySql.getContrastResultOfInsurance(req,importBatch,permit);
            //匹配状态设置
            setMatchStatus(pageData);
            result.setPageData(pageData);
            return result;
        }
    }

    @Override
    public ResultData getContrastResultOfSalesAdmin(RequestBase req, String permit) throws Exception {
        {
            ResultData result = new ResultData("true",null,null);
            String importBatch = importBatchSetting(); //导入批次
            PageData<Map<String, Object>> pageData =  saAdChRepSq.getContrastResultOfSalesAdmin(req,importBatch,permit);
            //匹配状态设置
            setMatchStatus(pageData);
            result.setPageData(pageData);
            return result;
        }
    }

    @Override
    public ResultData getContrastResultOfSalesConsultant(RequestBase req, String permit) throws Exception {
        {
            ResultData result = new ResultData("true",null,null);
            String importBatch = importBatchSetting(); //导入批次
            PageData<Map<String, Object>> pageData =  salesConsultantCheckRepositorySql.getContrastResultOfSalesConsultant(req,importBatch,permit);
            //匹配状态设置
            setMatchStatus(pageData);
            result.setPageData(pageData);
            return result;
        }
    }

    @Override
    public ResultData getRankingOfFinance(RankingRequest req) throws Exception{
        ResultData result = new ResultData("true",null,null);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        int dateNum = Integer.parseInt(String.valueOf((sdf.parse(req.endBatch).getTime() - sdf.parse(req.startBatch).getTime()) / (1000 * 3600 * 24))) + 1;
        List list = dataCheckRepository.getFinanceRankingList(req.permit, req.startBatch, req.endBatch, dateNum);
        PageData pageData = new PageData(list, 1);
        result.setPageData(pageData);
        return result;
    }

    @Override
    public ResultData getRankingOfInsurance(RankingRequest req) throws Exception{
        ResultData result = new ResultData("true",null,null);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        int dateNum = Integer.parseInt(String.valueOf((sdf.parse(req.endBatch).getTime() - sdf.parse(req.startBatch).getTime()) / (1000 * 3600 * 24))) + 1;
        List list = dataCheckRepository.getInsuranceRankingList(req.permit, req.startBatch, req.endBatch, dateNum);
        PageData pageData = new PageData(list, 1);
        result.setPageData(pageData);
        return result;
    }

    @Override
    public ResultData getRankingOfSalesAdmin(RankingRequest req) throws Exception {
        ResultData result = new ResultData("true",null,null);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        int dateNum = Integer.parseInt(String.valueOf((sdf.parse(req.endBatch).getTime() - sdf.parse(req.startBatch).getTime()) / (1000 * 3600 * 24))) + 1;
        List list = dataCheckRepository.getScAdminRankingList(req.permit, req.startBatch, req.endBatch, dateNum);
        PageData pageData = new PageData(list, 1);
        result.setPageData(pageData);
        return result;
    }

    @Override
    public ResultData getRankingOfSalesConsultant(RankingRequest req) throws Exception {
        ResultData result = new ResultData("true",null,null);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        int dateNum = Integer.parseInt(String.valueOf((sdf.parse(req.endBatch).getTime() - sdf.parse(req.startBatch).getTime()) / (1000 * 3600 * 24))) + 1;
        Lsh经销商表Model lsh经销商表Model = lsh经销商表Repository.getLshDistributorByPermit(req.permit);
        String cta = null;
        if (lsh经销商表Model != null) {
            cta = lsh经销商表Model.getCta();
            //呼伦贝尔利星(NHLL10)、鄂尔多斯之星(NORL10)
            if ("NHLL10".equals(lsh经销商表Model.getBmbs()) || "NORL10".equals(lsh经销商表Model.getBmbs())) {
                cta = "0";
            }
            //北三区(N3)
            else if ("N3".equals(lsh经销商表Model.get大区en())) {
                cta = "1";
            }
            //cta范围
            else if (!"".equals(cta) && cta != null){
                //cta = "2";
            }
            //单店
            else {
                cta = "3";
            }
        }
        List list = dataCheckRepository.getScRankingList(req.permit, req.startBatch, req.endBatch, dateNum, cta);
        PageData pageData = new PageData(list, 1);
        result.setPageData(pageData);
        return result;
    }

    @Override
    public void exportFinanceRanking(RankingRequest req, HttpServletResponse response) {
        try {
            ExcelReportContext context = new ExcelReportContext();
            context.setTemplate("templates/数据核验排名_金融专员模板.xlsx")
                    .setFileName("数据核验排名_金融专员.xlsx")
                    .setSource(exportFinanceRankingSource)
                    .setStaticSheet(false)
                    //.setClonableSheet(2)  读取第三个  sheet（不写默认为第一个sheet）
                    .addParam("req", req);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("报表[数据核验排名_金融专员]导出异常。", e);
        }
    }

    @Override
    public void exportInsuranceRanking(RankingRequest req, HttpServletResponse response) {
        try {
            ExcelReportContext context = new ExcelReportContext();
            context.setTemplate("templates/数据核验排名_保险专员模板.xlsx")
                    .setFileName("数据核验排名_保险专员.xlsx")
                    .setSource(exportInsuranceRankingSource)
                    .setStaticSheet(false)
                    //.setClonableSheet(2)  读取第三个  sheet（不写默认为第一个sheet）
                    .addParam("req", req);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("报表[数据核验排名_保险专员]导出异常。", e);
        }
    }

    @Override
    public void exportSalesAdminRanking(RankingRequest req, HttpServletResponse response) {
        try {
            ExcelReportContext context = new ExcelReportContext();
            context.setTemplate("templates/数据核验排名_销售行政模板.xlsx")
                    .setFileName("数据核验排名_销售行政.xlsx")
                    .setSource(exportSalesAdminRankingSource)
                    .setStaticSheet(false)
                    //.setClonableSheet(2)  读取第三个  sheet（不写默认为第一个sheet）
                    .addParam("req", req);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("报表[数据核验排名_销售行政]导出异常。", e);
        }
    }

    @Override
    public void exportSalesConsultantRanking(RankingRequest req, HttpServletResponse response) {
        try {
            ExcelReportContext context = new ExcelReportContext();
            context.setTemplate("templates/数据核验排名_销售顾问模板.xlsx")
                    .setFileName("数据核验排名_销售顾问.xlsx")
                    .setSource(exportSalesConsultantRankingSource)
                    .setStaticSheet(false)
                    //.setClonableSheet(2)  读取第三个  sheet（不写默认为第一个sheet）
                    .addParam("req", req);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("报表[数据核验排名_销售顾问]导出异常。", e);
        }
    }

    //对比结果输出：匹配状态设置
    private void setMatchStatus(PageData<Map<String, Object>> pageData) {
        List<Map<String, Object>> mapList = pageData.getData();
        for (Map<String,Object>  m : mapList) {
            //记录状态匹配设置  （matchResult: 00完全匹配 01部分匹配  02导入有实际无 03导入无实际有）
            if (!StringUtils.isNullOrBlank(m.get("compareResult"))) {
                if ("01".equals(m.get("compareResult").toString())){  // 导入有，实际无
                    m.put("matchResult","02");  //导入有，实际无

                } else if ("02".equals(m.get("compareResult").toString())) { // 导入有，实际有
                    if (!StringUtils.isNullOrBlank(m.get("totalScore"))) {
                        if("100.00".equals(m.get("totalScore").toString())) {
                            m.put("matchResult","00");  // 完全匹配
                        } else {
                            m.put("matchResult","01");  // 部分匹配
                        }
                    }

                } else if ("03".equals(m.get("compareResult").toString())) { // 导入无，实际有
                    m.put("matchResult","03");//  导入无，实际有
                } else {
                    m.put("matchResult",null);
                }
            }
        }
    }

    // 金融利率专用方法
    private BigDecimal getDeductionYearlyRate(BigDecimal arg0,BigDecimal arg1,int score){
        BigDecimal reDec = new BigDecimal(0);
        BigDecimal reScore = new BigDecimal(score);
        if (arg0 == null && arg1 == null) {
            return reDec;
        }
        if (arg0==null || arg1==null){
            return reScore;
        }
        // 分别四舍五入保留2位
        arg0 = arg0.divide(new BigDecimal(1),2,BigDecimal.ROUND_HALF_UP);
        arg1 = arg1.divide(new BigDecimal(1),2,BigDecimal.ROUND_HALF_UP);

        BigDecimal temp = arg0.subtract(arg1).abs();
        //大于设定误差则扣分，误差+-0.01
        if (temp.compareTo(new BigDecimal(Double.toString(DEFAULT_Difference_Yearly_Rate)))>0){
            reDec = reScore;
        }
        return reDec;
    }

    // 金额扣分方法:不在误差范围则扣分
    private BigDecimal getDeductionValue(BigDecimal arg0,BigDecimal arg1,int score){
        DecimalFormat format = new DecimalFormat("#");
        BigDecimal reDec = new BigDecimal(0);
        BigDecimal reScore = new BigDecimal(score);
        if ((arg0 == null && arg1 == null) || (arg0 == null && arg1 != null && "0".equals(format.format(arg1)) || (arg1 == null && arg0 != null && "0".equals(format.format(arg0))))) {
            return reDec;
        }
        if (arg0==null || arg1==null){
            return reScore;
        }
        BigDecimal temp = arg0.subtract(arg1).abs();
        //大于设定误差则扣分
        if (temp.compareTo(new BigDecimal(Double.toString(DEFAULT_Difference_Value)))>0){
            reDec = reScore;
        }
        return reDec;
    }

    // 数字扣分
    private BigDecimal getDeductionValue(Integer arg0,Integer arg1,int score){
        BigDecimal reDec = new BigDecimal(0);
        BigDecimal reScore = new BigDecimal(score);
        if (arg0 == null && arg1 == null) {
            return reDec;
        }
        if (arg0 == null || arg1 == null){
            return reScore;
        }
        if (arg0 != arg1){
            reDec = reScore;
        }
        return reDec;
    }


        // 文本扣分方法:不同则扣分
    private BigDecimal getDeductionValue(String arg0,String arg1,int score){
        BigDecimal reDec = new BigDecimal(0);
        BigDecimal reScore = new BigDecimal(score);
        if (Strings.isNullOrEmpty(arg0) && Strings.isNullOrEmpty(arg1)) {
            return reDec;
        }
        if (Strings.isNullOrEmpty(arg0) || Strings.isNullOrEmpty(arg1)){
            return reScore;
        } else if (!arg0.equals(arg1)){
            reDec = reScore;
        }
        return reDec;
    }

    // 日期扣分方法:不同则扣分
    private BigDecimal getDeductionValue(Date arg0,Date arg1,int score){
        BigDecimal reScore = new BigDecimal(score);
        if (arg0 == null && arg1 == null) {
            return new BigDecimal(0);
        }
        if (arg0==null || arg1==null){
            return reScore;
        }
        SimpleDateFormat sdf =  new SimpleDateFormat("yyyy-MM-dd");
        String date0 = sdf.format(arg0);
        String date1 = sdf.format(arg1);
        BigDecimal reDec = getDeductionValue(date0,date1,score);
        return reDec;
    }

    private java.sql.Date getDate(String key, Map map){
        if(map.get(key)!=null){
            if(map.get(key) instanceof java.sql.Date){
                return (java.sql.Date)map.get(key);
            } else if (map.get(key) instanceof Date){
                return new java.sql.Date(((Date) map.get(key)).getTime());
            } else if (map.get(key) instanceof String){
                if ("0000-00-00".equals(map.get(key)))
                    return null;
                else
                    return java.sql.Date.valueOf((String) map.get(key));
            }
            else{
                return null;
            }
        }else{
            return null;
        }
    }

    private String getString(String key, Map map){
        if(map.get(key)!=null){
            if(map.get(key) instanceof String){
                return (String)map.get(key);
            }else{
                return null;
            }
        }else{
            return null;
        }
    }

    private Integer getInteger(String key, Map map){
        if(map.get(key)!=null){
            if(map.get(key) instanceof Integer){
                return (Integer)map.get(key);
            } else if (map.get(key) instanceof String){
                return Integer.valueOf((String) map.get(key));
            }
            else{
                return null;
            }
        }else{
            return null;
        }
    }

    private BigDecimal getBigDecimal(String key, Map map){
        if(map.containsKey(key)){
            if(map.get(key) instanceof Double){
                return new BigDecimal((Double) map.get(key));
            } else if (map.get(key) instanceof BigDecimal){
                return (BigDecimal) map.get(key);
            } else{
                return null;
            }
        }else{
            return null;
        }
    }

    private void initResult(Map result) {
        result.put("result",false);
        result.put("message","导入失败!");
    }

    private String importBatchSetting() {
        //导入批次
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        return sdf.format(new Date());
    }

    @Override
    public void exportScFalseRateReport (HttpServletResponse response, String permit, String exportStartDate, String exportEndDate) {
        ExcelReportContext context = new ExcelReportContext();
        try {
            context.setTemplate("templates/LSH_经销商_数据核验_错误率报告_销售顾问.xlsx")
                    .setFileName("经销商_数据核验_错误率报告_销售顾问.xlsx")
                    .setSource(ExportDataCheckScFalseRateReportSource)
                    .setStaticSheet(true)
                    .addParam("permit", permit)
                    .addParam("exportStartDate", exportStartDate)
                    .addParam("exportEndDate", exportEndDate);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("导出数据核验--销售顾问错误率报告程序异常", e);
        }
    }



    public void exportFinanceFalseRateReport (HttpServletResponse response, String permit, String exportStartDate, String exportEndDate) {
        ExcelReportContext context = new ExcelReportContext();
        try {
            context.setTemplate("templates/LSH_经销商_数据核验_错误率报告_金融.xlsx")
                    .setFileName("经销商_数据核验_错误率报告_金融.xlsx")
                    .setSource(ExportDataCheckFinanceFalseRateReportSource)
                    .setStaticSheet(true)
                    .addParam("permit", permit)
                    .addParam("exportStartDate", exportStartDate)
                    .addParam("exportEndDate", exportEndDate);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("导出数据核验--销售顾问错误率报告程序异常", e);
        }
    }

    public void exportInsuranceFalseRateReport (HttpServletResponse response, String permit, String exportStartDate, String exportEndDate) {
        ExcelReportContext context = new ExcelReportContext();
        try {
            context.setTemplate("templates/LSH_经销商_数据核验_错误率报告_保险.xlsx")
                    .setFileName("经销商_数据核验_错误率报告_保险.xlsx")
                    .setSource(ExportDataCheckInsuranceFalseRateReportSource)
                    .setStaticSheet(true)
                    .addParam("permit", permit)
                    .addParam("exportStartDate", exportStartDate)
                    .addParam("exportEndDate", exportEndDate);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("导出数据核验--销售顾问错误率报告程序异常", e);
        }
    }

    public void exportScAdminFalseRateReport (HttpServletResponse response, String permit, String exportStartDate, String exportEndDate) {
        ExcelReportContext context = new ExcelReportContext();
        try {
            context.setTemplate("templates/LSH_经销商_数据核验_错误率报告_销售行政.xlsx")
                    .setFileName("经销商_数据核验_错误率报告_销售行政.xlsx")
                    .setSource(ExportDataCheckScAdminFalseRateReportSource)
                    .setStaticSheet(true)
                    .addParam("permit", permit)
                    .addParam("exportStartDate", exportStartDate)
                    .addParam("exportEndDate", exportEndDate);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("导出数据核验--销售顾问错误率报告程序异常", e);
        }
    }

    @Override
    public void exportScContrastReport(HttpServletResponse response, String permit, String startDate, String endDate) {
        ExcelReportContext context = new ExcelReportContext();
        try {
            context.setTemplate("templates/" + GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验_销售顾问对比报告.xlsx")
                    .setFileName(GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验_销售顾问对比报告.xlsx")
                    .setSource(ExportDataCheckScContrastReportSource)
                    .setStaticSheet(true)
                    .setAutoRowExtended(true)
                    .setStartCellIndex(0)
                    .setEndCellIndex(35)
                    .addParam("permit", permit)
                    .addParam("startDate", startDate)
                    .addParam("endDate", endDate);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("导出数据核验--销售顾问对比报告程序异常", e);
        }
    }

    @Override
    public void exportFinanceContrastReport(HttpServletResponse response, String permit, String startDate, String endDate) {
        ExcelReportContext context = new ExcelReportContext();
        try {
            context.setTemplate("templates/" + GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验_金融对比报告.xlsx")
                    .setFileName(GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验_金融对比报告.xlsx")
                    .setSource(ExportDataCheckFinanceContrastReportSource)
                    .setStaticSheet(true)
                    .setAutoRowExtended(true)
                    .setStartCellIndex(0)
                    .setEndCellIndex(27)
                    .addParam("permit", permit)
                    .addParam("startDate", startDate)
                    .addParam("endDate", endDate);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("导出数据核验--金融对比报告程序异常", e);
        }
    }

    @Override
    public void exportInsuranceContrastReport(HttpServletResponse response, String permit, String startDate, String endDate) {
        ExcelReportContext context = new ExcelReportContext();
        try {
            context.setTemplate("templates/" + GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验_保险对比报告.xlsx")
                    .setFileName(GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验_保险对比报告.xlsx")
                    .setSource(ExportDataCheckInsuranceContrastReportSource)
                    .setStaticSheet(true)
                    .setAutoRowExtended(true)
                    .setStartCellIndex(0)
                    .setEndCellIndex(17)
                    .addParam("permit", permit)
                    .addParam("startDate", startDate)
                    .addParam("endDate", endDate);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("导出数据核验--保险对比报告程序异常", e);
        }
    }

    @Override
    public void exportScAdminContrastReport(HttpServletResponse response, String permit, String startDate, String endDate) {
        ExcelReportContext context = new ExcelReportContext();
        try {
            context.setTemplate("templates/" + GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验_销售行政对比报告.xlsx")
                    .setFileName(GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验_销售行政对比报告.xlsx")
                    .setSource(ExportDataCheckScAdminContrastReportSource)
                    .setStaticSheet(true)
                    .setAutoRowExtended(true)
                    .setStartCellIndex(0)
                    .setEndCellIndex(27)
                    .addParam("permit", permit)
                    .addParam("startDate", startDate)
                    .addParam("endDate", endDate);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("导出数据核验--销售行政对比报告程序异常", e);
        }
    }

    @Override
    public void exportScRankingListReport(HttpServletResponse response, String permit, String startDate, String endDate) {
        ExcelReportContext context = new ExcelReportContext();
        try {
            context.setTemplate("templates/" + GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验排名_销售顾问.xlsx")
                    .setFileName(GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验排名_销售顾问.xlsx")
                    .setSource(ExportDataCheckScRankingListReportSource)
                    .setStaticSheet(true)
                    .addParam("permit", permit)
                    .addParam("startDate", startDate)
                    .addParam("endDate", endDate);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("导出数据核验--销售顾问排名报告程序异常", e);
        }
    }

    @Override
    public void exportFinanceRankingListReport(HttpServletResponse response, String permit, String startDate, String endDate) {
        ExcelReportContext context = new ExcelReportContext();
        try {
            context.setTemplate("templates/" + GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验排名_金融.xlsx")
                    .setFileName(GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验排名_金融.xlsx")
                    .setSource(ExportDataCheckFinanceRankingListReportSource)
                    .setStaticSheet(true)
                    .addParam("permit", permit)
                    .addParam("startDate", startDate)
                    .addParam("endDate", endDate);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("导出数据核验--金融排名报告程序异常", e);
        }

    }

    @Override
    public void exportInsuranceRankingListReport(HttpServletResponse response, String permit, String startDate, String endDate) {
        ExcelReportContext context = new ExcelReportContext();
        try {
            context.setTemplate("templates/" + GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验排名_保险.xlsx")
                    .setFileName(GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验排名_保险.xlsx")
                    .setSource(ExportDataCheckInsuranceRankingListReportSource)
                    .setStaticSheet(true)
                    .addParam("permit", permit)
                    .addParam("startDate", startDate)
                    .addParam("endDate", endDate);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("导出数据核验--保险排名报告程序异常", e);
        }

    }

    @Override
    public void exportScAdminRankingListReport(HttpServletResponse response, String permit, String startDate, String endDate) {
        ExcelReportContext context = new ExcelReportContext();
        try {
            context.setTemplate("templates/" + GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验排名_销售行政.xlsx")
                    .setFileName(GetConfigureInfoUtils.getConfigureInfo("file_group") + "_经销商_数据核验排名_销售行政.xlsx")
                    .setSource(ExportDataCheckScAdminRankingListReportSource)
                    .setStaticSheet(true)
                    .addParam("permit", permit)
                    .addParam("startDate", startDate)
                    .addParam("endDate", endDate);
            excelReportService.export(context, response);
        } catch (Exception e) {
            logger.error("导出数据核验--销售行政排名报告程序异常", e);
        }

    }

    // 金融、保险、销售行政  检查数据重复
    private boolean validRepeatedData(ResultData result, List<Map<String, Object>> mapList) {
        boolean flag = false;  // 是否有重复的行
        List<Integer> repeatRows = new ArrayList<>();  // 已经重复的行
        StringBuffer repeatMessages = new StringBuffer(); // 所有重复行提示语
        for (int i= 0;i < mapList.size();i++) {

            Integer rowNum1 = (Integer) mapList.get(i).get("row") - (Integer) mapList.get(i).get("titleLine");  // 当前数据在表格中的行号
            if (!repeatRows.contains(i)) {   // 已经重复的行不再检测
                List<Integer> rows = new ArrayList<>();
                String message = "第" + rowNum1 + "行%s，数据重复；";
                boolean isRepeat = false;  // 当前行是否重复

                // 以车架号为唯一判断
                for (int j=i+1;j<mapList.size();j++) {
                    Integer rowNum2 = (Integer) mapList.get(j).get("row") - (Integer) mapList.get(j).get("titleLine");  // 当前数据在数据行的行号
                    if (mapList.get(i).get("vin").equals(mapList.get(j).get("vin"))) {
                        flag = true;
                        isRepeat = true;
                        rows.add(rowNum2);
                        repeatRows.add(j);
                    }
                }

                String str = "";
                if (!rows.isEmpty()) {
                    for (Integer row : rows) {
                        str = str + "与第" + row + "行";
                    }
                }
                if (isRepeat) {   // 当前行重复，则把当前行加入到提示语中
                    repeatMessages.append(String.format(message,str));
                }
            }
        }
        // 有重复数据
        if (flag) {
            result.setResult("false");
            result.setMessage("导入失败。" + repeatMessages.toString() + "请删除其中重复条。");
            return true;
        }
        return false;
    }

    // 销售顾问  查看是否有重复数据
    private boolean validSCRepeatedData(ResultData result, List<Map<String, Object>> mapList) {
        boolean flag = false;  // 是否有重复的行
        List<Integer> repeatRows = new ArrayList<>();  // 已经重复的行
        StringBuffer repeatMessages = new StringBuffer(); // 所有重复行提示语
        for (int i= 0;i < mapList.size();i++) {

            Integer rowNum1 = (Integer) mapList.get(i).get("row") - (Integer) mapList.get(i).get("titleLine");  // 当前数据在表格中的行号
            if (!repeatRows.contains(i)) {   // 已经重复的行不再检测
                List<Integer> rows = new ArrayList<>();
                String message = "第" + rowNum1 + "行%s，数据重复；";
                boolean isRepeat = false;  // 当前行是否重复


                // 如果是订单 （车架号不为空，则为订单。以客户电话和车架号为唯一判断）
                if (!StringUtils.isNullOrBlank(mapList.get(i).get("vin"))) {

                    for (int j=i+1;j<mapList.size();j++) {
                        Integer rowNum2 = (Integer) mapList.get(j).get("row") - (Integer) mapList.get(j).get("titleLine");  // 当前数据在数据行的行号
                        if (!StringUtils.isNullOrBlank(mapList.get(j).get("vin"))) {  // 只和订单比较
                            if (mapList.get(i).get("customTel").equals(mapList.get(j).get("customTel")) && mapList.get(i).get("vin").equals(mapList.get(j).get("vin"))) {
                                flag = true;
                                isRepeat = true;
                                rows.add(rowNum2);
                                repeatRows.add(j);
                            }
                        }
                    }
                }
                // 如果是意向订单 （车架号为空，则为意向订单。以客户电话和意向车型和意向金为唯一判断）
                else  {
                    for (int j=i+1;j<mapList.size();j++) {
                        Integer rowNum2 = (Integer) mapList.get(j).get("row") - (Integer) mapList.get(j).get("titleLine");  // 当前数据在数据行的行号
                        if (StringUtils.isNullOrBlank(mapList.get(j).get("vin"))) {  // 只和意向订单比较
                            if (mapList.get(i).get("customTel").equals(mapList.get(j).get("customTel")) && mapList.get(i).get("intentCar").equals(mapList.get(j).get("intentCar")) && mapList.get(i).get("ioAmount").equals(mapList.get(j).get("ioAmount"))) {
                                flag = true;
                                isRepeat = true;
                                rows.add(rowNum2);
                                repeatRows.add(j);
                            }
                        }
                    }
                }

                String str = "";
                if (!rows.isEmpty()) {
                    for (Integer row : rows) {
                        str = str + "与第" + row + "行";
                    }
                }
                if (isRepeat) {   // 当前行重复，则把当前行加入到提示语中
                    repeatMessages.append(String.format(message,str));
                }
            }
        }
        // 有重复数据
        if (flag) {
            result.setResult("false");
            result.setMessage("导入失败。" + repeatMessages.toString() + "请删除其中重复条。");
            return true;
        }
        return false;
    }

    private boolean validUser(String permit, ResultData result, 用户Model user) {
        if (null == user) {
            result.setResult("false");
            result.setMessage("导入失败，用户不存在!");
            logger.error(String.format("导入失败，用户不存在!经销商许可为:%s",permit));
            return true;
        }
        return false;
    }
}
